Basic Excel Training Module 3k6qd

BASIC EXCEL TRAINING Business Quality Development

Objective  To provide the participants with a working understanding of

the basic features of MS Excel

BASIC EXCEL TRAINING Anna Torrijos

B i EXCEL® Topics Basic T i 

Introduction to Excel



AutoFill



Working with Rows ands Columns



Using Formula & Fu tio Functions



Formatting Cells





Conditional Formatting

Absolute Cell Addressing



Simple Charts Simple Charts



Shortcuts


Introduction to Excel


Viewing the Excel Window and Task Panes

 When yyou start Excel, the Excel p program g window opens p with a blank

workbook-ready for you to begin working. This view includes features such as: Menu Bar, Status Bar, Toolbars, Formula Bar, Cell Addresses, Mouse Pointer, Worksheet k h Tab, b andd the h Select l Allll Button


The Excel Window Menu Bar Name Box Tool Bars Formula Bar Columns

Rows Cells

Worksheet Tab BASIC EXCEL TRAINING Status Bar Anna Torrijos

Working with Rows and Columns


Adjust Column Width & Row Height  Click the column or row header button for the first column or the row    

you want to adjust If you want, drag to select more columns or rows Right click the selected column(s) or ro Right-click row(s), (s) and then click Column Width or Row Height Type a new column width or row height in points Click OK


Adjust Column Width & Row Height Using Mouse  Position the mouse pointer on the right edge of the column header

button or the bottom edge of the row header button for the column or row you want to change.  When the mouse p pointer changes g to a double-headed arrow,, click and drag the pointer to a new width or height.


Deleting Columns or Rows  Select the column header(s) or row header(s) button that you want to

delete delete.  Click the Edit menu, and then click delete.  For shortcuts, shortcuts just highlight the row(s) ro (s) or the column(s) that you ou want ant to delete, Right-click and then click Delete


Inserting Columns or Rows  Click to the right of the location of the new column you want to insert

To insert a row, row click the row immediately below the location of the row you want to insert  Click the Insert Menu, Menu and then click Columns or Rows Rows.  For shortcuts, just highlight location of the row(s) or the column(s) that yyou want to insert,, Right-click g and then click Insert


Rows & Columns  Adjust column width & row height  Using a value  Using the mouse

 Delete columns or rows  Using the edit menu  Using the mouse

 Insert columns or rows  Using U i the th iinsertt menu  Using the mouse BASIC EXCEL TRAINING Anna Torrijos

Formatting Cells


Formatting Cells  Applying formats  From the formatting toolbar  Currency, Currency % , date formats  Custom formats

 Text Control  Wrap text  Shrink to fit  Merge cells

 Borders and shading


Applying Currency Formats  Select a cell or range with the number(s) you want to

format.  Click one of the buttons on the Formatting toolbar to apply

that attribute to the selected range:  Currency C St Style l  Percent Style  Comma Style  Increase / Decrease Decimal

(Note: You can apply multiple attributes to the range.)


Applying Date Formats  Select a cell or range that contains the date format you

want to change  Click the Format menu, and then click cells.  Click the Number tab.  Click Date  Click the Date or time format.  Click OK


Merge and Center  Select a cell or range you want to change  Click the Format menu, and then click cells  Click the Alignment Tab  Click to select one or more Text Control check boxes:  Wrap Text moves the text to multiple lines within a cell  Shrink to Fit reduces character size to fit within a cell  Merge M C Cells ll combines bi selected l d cellll iinto a single i l cellll


Borders and Shading  Select a cell or range to which you want to apply borders  Click the Format Menu, and then click Cells  Click Cli k the h B Border d T Tabb  Select a Line style  Click the Color list arrow, arrow and then click a color for the border  If you want a border on the outside of a cell or range, click Outline. If between cells,

click Inside. If you want to remove border, click None.  To set a custom border, click a border button, or click the Preview Border box where

you want o add the border.  Click OK. OK


Conditional Formatting


Setting up Conditional Formats • • •

Select the cells yyou want to highlight. g g On the Format menu, click Conditional Formatting. Do one of the following:

To use values in the selected cells as the formatting criteria, click Cell Value Is. • To use a formula as the formatting criteria (to evaluate ata or o a condition co t o other ot e than t a the t e values va ues in se selected ecte ce cells), s), data click Formula Is Click Format. Select the font style, style font color, color underlining underlining, borders borders, shading shading, or patterns you want to apply. Microsoft Excel applies the selected formats only if the cell value meets the condition or if the formula returns a value of TRUE. To add another condition,, click Add,, and then repeat p steps p 3 through g 5.You can specify up to three conditions. If none of the specified conditions are true, the cells keep their existing formats. •

• •

•


Conditional Formatting  Setting up conditional formats  Cell value is  Formula is

 Clearing all formats


Using Formula in Conditional Formats

• To use a formula as the formatting

criteria (to evaluate data or a condition other than the values in selected cells), click Formula Is  Tip: p: To remove all conditional formats as well as all other cell

formats for selected cells, point to Clear on the Edit menu, and then click Formats.


Auto Fill


Using AutoFill  Select the first cell in the range you want to fill  Enter the starting value to be repeated  Position P iti th the pointer i t on th the lower-right l i ht corner off th the selected l t d

cell. The pointer changes to the fill handle ( a black plus sign)  Drag D the h fill hhandle dl over the h range in i which hi h you want the h value repeated


Fast AutoFill Tips  Suppose you have a column full of values in column A. For

    

instance, you have values in the cells A3 through A417. Now, suppose you want to fill adjacent cells in column B with sequential values, l 1 th throughh 415 415. T Try this: thi In cell B3, place the value 1. In cell B4, B4 place the value 22. Select the range B3:B4. Double-click on the AutoFill handle. handle That's it! No dragging at all, and you now have cells B5:B417 filled q values !!! with sequential


Auto Fill  Using auto fill  Fill handle

 Fast auto fill tips  

Initial selection

Extended series

1, 2, 3

4, 5, 6

9:00

10:00, 11:00, 12:00

Mon

Tue, Wed, Thu

Monday

Tuesday, Wednesday, Thursday

Jan

p Feb,, Mar,, Apr

Jan, Apr

Jul, Oct, Jan

Jan-99, Apr-99

Jul-99, Oct-99, Jan-00

p 15-Jan,, 15-Apr

15-Jul,, 15-Oct

1999, 2000

2001, 2002, 2003

1-Jan, 1-Mar

1-May, 1-Jul, 1-Sep,...

Qtr3 (or Q3 or Quarter3)

Qtr4, Qtr1, Qtr2,...

text1, textA

text2, textA, text3, textA,...

1st Period

2nd Period, 3rd Period,...

Product 1

Product 2, Product 3,...


Using Formula and Functions


How to Enter Formula  Click the cell where you want to enter formula  Type =.  Enter the first argument, argument which can be a number or cell

reference  Enter an arithmetic operator p  Repeat steps 4 and 5 as needed to complete the formula  Click the enter button on the formula bar,, or just j ppress Enter


Formula & Functions  Enter a formula  Insert function window  Using the formula bar  Edit directly in cell

 AutoSum  Basic functions  Sum & Average A erage  Count & Counta  Min & Max


Edit a Formula Using the Formula Bar  The q quickest wayy to do this is simply p y to press p F2. This tells Excel that yyou want

    

to edit the contents of a cell. When you press F2, it is highly possible that Excel will simply expand the information in the cell and allow you to edit the y don't like this behavior, but instead information directlyy in the cell itself. If you want F2 to activate the Formula bar, follow these steps: Choose Options from the Tools menu. Excel displays the Options dialog box. Make sure the Edit tab is selected. selected Clear the Edit Directly In Cell check box. Click on OK. Now, whenever you press F2, the Formula bar is selected and you can edit the formula there.


AutoSum  Click the cell where you want to display calculation  Click the AutoSum button on the Standard toolbar  Click Cli k Enter E t


Functions?  The Insert Function feature simplifies the process of writing

a formula that calculates various pieces of data by organizing Excel’s ce s built-in u t formulas o u as into to catego categories es so that t at tthey ey aaree easy to find and use


Sum, Count, Average g Using SUM Function Enter the formula =SUM(range), where range is any range. This sums the numerical values i the in h range

Using COUNT Function Enter the formula =COUNT(range), where range is any range. This produces the number of numerical values in the range

Using AVERAGE Function Enter the formula =AVERAGE(range) where range is any range. This produces the average of the numerical values in the range BASIC EXCEL TRAINING Anna Torrijos

Example

In the diagram above the three functions used are as follows: = SUM(B4:B7)

352 in cell B9

= AVERAGE(B4:B7) ( )

88 in cell B10

= COUNT(B4:B7)

4 in cell B11 BASIC EXCEL TRAINING Anna Torrijos

Min and Max Using MAX and MIN Function

Enter the formula =MAX(range) or =MIN(range) where range is any range. These produce th obvious the b i results: lt the th maximum i (or ( minimum) i i ) value l in i the th range


Converting Text 

Use the PROPER function to convert text to its proper case. The Proper function is in the Text category in the Insert Function dialog box. Example: Text in uppercase: GEORGE W W. BUSH (in A1 Cell) The function: =PROPER(A1) Result: George W. Bush



Use the UPPER & LOWER function to convert text to uppercase & lowercase. The UPPER and LOWER function is in the Text category in the Insert Function dialog box. Example: Text in lowercase: george w. w bush (in A1 Cell) The function: =UPPER(A1) Result: GEORGE W. BUSH Text in Upper case: C:\MY DOCUMENTS\EXCELTIP.XLS (in A1 Cell) The Function: =LOWER(A1) Result - Text in Lower case : c:\my documents\exceltip.xls


ing text using formula  Much like writing a formula to text, as described, the Concatenate function

allows you to merge values from multiple cells into a single cell.  Concatenating text in Excel is easy. For instance, if you have something in cell A2 and

you want to concatenate it with what is in cell B2, you can do so with this formula: =A2 & B2  You could include the // delimiters between the two values by simply adding them

into the proper place: place =A2 & "//" & B2


Absolute cell addressingg Using Absolute / Relative Reference

Relative references - When you create a formula, references to cells or ranges are usually based on their position relative to the cell that contains the formula. Absolute references - If you don don'tt want Excel to adjust references when you copy a formula to a different cell, use an absolute reference. Switching between relative and absolute references If you created a formula and want to g relative references to absolute (and ( vice versa), ), select the cell that contains the change formula. In the formula bar, select the reference you want to change and then press F4 Key. Key Each time you press F4, Excel toggles through the combinations: Absolute column and Absolute row (for example, $C$1); Relative column and Absolute row (C$1); Absolute column and Relative row ($C1); Relative column and Relative row (C1). BASIC EXCEL TRAINING Anna Torrijos

Charts  Select the data range g yyou want to chart. Make sure yyou include the data, and the column       

and row labels Click the Chart Wizard button on the Standard toolbar Click a Chart Type Click a Chart Sub Type Click Next the Data range, and then select to plot the data series in rows or columns Click Next Click on Chart Options Tab      

Titles tab Axes Tab Gridlines Tab Legend Tab D t Labels Data Lb l T Tabb Data Table Tab


Charts continued…  Preview the options, options then click Next  Select to place on a new sheet or as an embedded object  Click Cli k Finish Fi i h


Make that Chart Quickly!  Sometimes ggoingg through g the Chart Wizards seems to take forever. If you want to put a chart into your workbook very quickly, you can follow these two simple steps:  Select the table on which the chart is to be based. based  Press F11.  That's it! Excel inserts a new Chart worksheet before the current worksheet, and bases the chart on the data in your table and the default chart type you have set up. At this point you can pperform whatever customization you y desire on the chart and work with it as normal.


Best Shortcuts Keys in Excel Ctrl+Z

Undo

Ctrl+C

Copy,

Enter, Ct l V Ctrl+V Ctrl+X

Paste, M lti l P Multiple Paste, t Cut

Ctrl+F, Ctrl+H

Find, Find&Replace

Ctrl+P, Ctrl+S, Ctrl+F4, Alt+F4

Print, Save, Close, Close Excel

Ctrl+Arrow

Move to edge of region

Ctrl+Shift+End

Ctrl+Shift+Home

Ctrl+Page Down Ctrl+Page Up

Ctrl+Tab Ctrl+*

Select current region

Ctrl+A

Select all cells

Ctrl+Home Ctrl+End

Select A1 A1, Select last cell in used range

Select from active cell to last cell in used range.

Select from active cell to A1

Move to the next sheet, Move to the previous sheet

Shift+F3

Paste function window

=+FunctionName+Ctrl +A

Insert new function

Alt+F11

Open VBE

Ctrl+Shift+Enter

Array formula

Ctrl+F3, F3

Define name, Paste name

Ctrl+Spacebar p Shift+Spacebar

Select columns, Select rows

Ctrl+1, Ctrl+B, Ctrl+U

Format cells, Bold, Underline

Ctrl+; , Ctrl+shift+:

Current date, Current time

Move to next open workbook

Ctrl+N

Open new workbook

Shift+F11

Insert new worksheet


Using keyboard shortcuts 

To open the Insert Function dialog box: Select an empty cell and press Shift+F3. p a Function Arguments g dialogg box: To open Select a cell containing a formula and press Shift+F3.



To insert a new Formula into a cell using the Function Arguments dialog box:

1. Select an empty cell, and then type the = sign. 2. Type the formula name and press Ctrl+A. Example: type =SUM, and press Ctrl+A to open the Function Arguments window  To insert a formula by typing it while being guided by the formula syntax tooltip: 1. Select an empty cell, and then type the = sign followed by the formula name and a ( sign. Example: type = Average, and then press Ctrl+Shift+A.You will now see the syntax of the function.


Thank you !!!


Basic Excel Training Module 3k6qd

Overview 3e4r5l

More details w3441

Related Documents 3m3m1z

Basic Excel Training Module 3k6qd

Advanced Excel Training Module i861

Basic Hac Training Module 3n3u13

How To Prepare A Basic Training Module 602yx

Module 8- Basic Aerodynamics.doc 552w52

Basic Calligraphy Module 6s6e1t

More Documents from "Rodgie Tolentino" 2e5l3h

Copc-2000 Csp Gold j4g5e

Advanced Excel Training Module i861

Basic Excel Training Module 3k6qd

Grade 9 Chemistry Textbook 4g6w4q

Silangang Asya 5a692k

Constitutional Law Reviewer 3kn3a