Computer Tutorials II: Microsoft Excel

We continue with our computer tutorials from Flagstaff Public Library- this one covers the spreadsheet Microsoft Excel.  Here I cover the 2010 version because at the time the library had Microsoft Office 2010.  The current (2013) version is not much different from the 2010 edition, although my next blog entry discusses Excel 2013.

 Step 1: Open Microsoft Excel

There are a number of ways to open Microsoft Excel:

1.     Using the mouse, click the Start button.    

2.     Click All Programs.

3.     Click Microsoft Office (name of office suite)

4.     Click Microsoft Office Excel 2010.

OR

On the desktop (main screen), click on the icon (or symbol) for Microsoft Excel.

Once you have opened Excel, the screen should look like this:

Step 2: Knowing How to Use an Excel Worksheet/Definitions

Cell- a box where you enter any spreadsheet data.  

Cell address- the cell location determined by the letter and number of the intersecting column and row.  Example: A1.

Column- a vertical line of cells with a distinct letter identifying it.  Examples: A, B, C.

Row- a horizontal line of cells with a distinct number identifying it.  Examples: 1, 2, 3.

Formula- a set of numbers and values that performs a calculation.  In Excel, formulas begin with an equal sign (=) and continue with the formula text.  Example: =B1+B2 (The number in cell B1 adds the number in cell B2) 

Range- a group of 2 or more cells used in making charts.

Spreadsheet- a computer program used for manipulating numbers and statistics in rows and programs (Excel).

Workbook- a spreadsheet file that consists of one or more worksheets.

Worksheet- the area where data and formulas are entered.

Worksheet tab- displays the worksheet name at the bottom of the screen (Sheet 1, Sheet 2, Sheet 3) which the user can navigate between the worksheets.

Excel Window Controls- several commands to minimize, maximize, restore, and close Excel.  These features are useful when working with several worksheets.

Step 3: Creating Charts and Graphs in Microsoft Excel

EXERCISE: Create a graph using the following information:

	1A	7G
Jan	1	6
Feb	0	5
Mar	3	5
Apr	0	0
May	2	7
Jun	2	9
Jul	3	12
Aug	2	13
Sep	5	15
Oct	3	15
Nov	1	14
Dec	0	14

1. Type the above information in Excel, starting with Jan in cell A2.  Type the rest of the months (Feb, Mar) in the A column.  Type 1A in cell B1 and 7G in cell C1.  Type the data in the corresponding B and C columns.  The table should look like the following:

 

2. Select the cell range with the mouse.  You can also hold down the Control and A keys simultaneously to select the range. (This is a shortcut for the “Select All” command).  The entire range is highlighted. 

3. Click the Insert tab on the ribbon. 

4. Select a chart type.  In this case, we are going to use the Column option.  Excel lists several configurations for the Column option.

5. Select the first configuration under 3-D Column. 

6. The chart should look like this: 

Feel free to experiment with other configurations, but return to the Column option and 3-D Column.

7. The next step is to add a chart title.  Click the chart with the mouse.

8. Click the Layout tab on the ribbon.

9. Click Chart Title.

10. Click Above Chart and type the title Work Accidents at Springfield Nuclear Plant by Sector, 2010.  The chart should look like this:

11. To add axis titles, click Axis Titles. 

12. Click Primary Horizontal Axis Title.

13. Click Title Below Axis and type the title Month.

14. Click Primary Vertical Axis Title.

15. Click Rotated Title and type the title Accidents.

16. To add a legend (or key) identifying the colors of each data series (1A, 7G) click the chart and click the Layout tab.  Click Legend and the position you want to use for the chart legend.

The final chart should look like the following:

KEY POINT: When you type data anywhere in the range (for example, change 14 to 15 in cell C13) the graph data changes as well.

Formulas

You can manipulate data in Excel using formulas, or equations.  Remember that formulas begin with an equal sign (=).  In Excel, most formulas have a built-in function like SUM or AVERAGE.

The following mathematical operators are used in formulas:

+ (plus sign) for addition

- (minus sign or hyphen) for subtraction

* (asterisk) for multiplication

/ (slash) for division

^ (caret) for raising a number to an exponential power

EXAMPLE: Type TOTAL in cell A14.  Click on cell B14 and type the following in the Formula (fx) bar: 

=(B2+B3+B4+B5+B6+B7+B8+B9+B10+B11+B12+B13)

Click on the chart when done.

·      The simplest way would be to use the formula =SUM(B2:B13).  Click on cell B14 and type =SUM(B2:B13) in the fx bar. 

·      Go to cell C14 and repeat the same steps, using the formula =SUM(C2:C13).

KEY POINT: Microsoft Excel has the AutoSum feature, which allows you to add numbers without entering data.  Click a cell where you want the sum to appear and click the Sum button, or Greek sigma symbol () on the ribbon.  If you want to use a function besides SUM, click the Sum  and click on the operation you want to use.

EXAMPLE: Click on cell C14 (total accidents for Sector 7G).

OPTIONAL: Adding data labels to a chart (makes a chart easier to read)

1.     Click the chart “Work Accidents at Springfield Nuclear Plant by Sector, 2010”

2.     Click the Layout tab.

3.     Click Data Labels and select the position you want to use for the data labels.

OPTIONAL: Applying a conditional format to a range (a conditional format applies to only certain cells that meets certain criteria)

1.     Using the mouse, select the range.

2.     Click the Home tab.

3.     Click Conditional Formatting.

4.     Click Highlight Cell Rules.  Click the operator (Greater Than…, Less Than…) you want to use and type the value you want to use.

5.     Click the formatting you want to use.

6.     Click OK.

Now we are going to work in a new worksheet.  On the worksheet tab, click Sheet 2.

Create a chart/graph based on the previous exercise.

Step 4: Filtering and Sorting Data

Click on Worksheet 2.

EXERCISE: Type the following information, starting in Cell A1:

Customer ID	Customer Name	City	State	Amount Spent
15642	Gumble, Barney	Springfield	IL	$25.28
21123	Szlac, Moe	Springfield	IL	$67.18
29077	Eisenreich, John	Philadelphia	PA	$107.98
33211	Carlson, Carl	Springfield	IL	$30.92
43766	Richardson, Larry	Portsmouth	NH	$200.12
43766	Webster, David	Flagstaff	AZ	$66.20
56777	Hunter, Diane	Los Angeles	CA	$182.50
77992	Flanders, Ned	Springfield	IL	$12.00
88556	Henderson, Heather	Denver	CO	$148.00
99996	Gomez, Jennifer	Trenton	NJ	$244.11

·      To sort data, highlight the A column (Customer ID).   Click on the Data tab.  Click either the Sort Ascending or Sort Descending button.  If we select Sort Descending, Excel will start with the bottom ID (99996). 

·       We will use the Auto Sum feature here.  Click on cell E12.  Click the  button and the drop-down button to AVERAGE. (We are taking the average amount of money spent).

·      To filter data, highlight the B column (Customer Name).  Click on the Data tab and click the Filter button on the ribbon.  A drop-down button appears in cell B1 (Customer Name):

·      Click on the drop-down button.  Unselect the Select All option.  Select “Carlson, Carl” and “Flanders, Ned.” Notice how only the data affiliated with these names is displayed.

Other Excel Essentials

1.     Resizing columns: Often the text or number does not fit in a cell.  Position the mouse on the border between the columns.  The cursor becomes a black cross.  Drag the cursor to the right.  To change the width of all columns, hold down the Control and A keys and click the Home tab.  Click Format and click Column Width.  Type the width in the Column width text box and click OK.

2.     Changing the row height: Select a range including at least one cell in every row you want to resize (1, 2, 3).  Click the Home tab and click Format.  Click Row Height.  A Row Height dialog box appears and type the desired height.  Click OK.

3.     Applying a style to a cell: Select a cell or range to format.  Click the Home tab and click Cell Styles.  Click the style you want.

4.     Inserting pictures and graphics: You can insert images into a worksheet, including clip art (artwork that comes with Excel) and photos.  To insert clip art, click the Insert tab and click Clip Art.  Choose an image in the Clip Art box and click Go.  To insert photos, click the Insert tab and click Picture.  Open the folder that contains the photo and click the photo.  Click Insert.

5.     Finding and replacing text: Click the Home tab and click the Find & Select option.  If you want to find text, click Find.  The Find and Replace box appears and type the text in the Find what text box.  To find more recurrences, click Find Next.  To replace text, click Replace.  In the Find What text box, type the text you want to find and the text you want to replace in the Replace with text box.  Click Close when you are done with either option.

6.      Printing an Excel workbook: Click on the File tab and click Print. (If you want to print a single worksheet, click the tab on the Worksheet tab).  Once you have made all your settings, click Print.

7.     Deleting data: If you want to delete a range, select the range that you are going to delete.  Click the Home tab and click Delete.  Click Delete Cells.  Pick your option (often _Shift cells up_) and click OK.

If you want to erase data from a single cell, select the cell with the mouse and click the Home tab.  Click Clear and Click Contents. 

8.     Getting help in Microsoft Excel: Click the  icon at the top right screen (next to the  icon).  Type a word or phrase related to your question- “change column width.” Press the ENTER key- Excel displays a list of topics most closely related to your question.  Click on the desired topic.  To exit the Help window click X in the top right corner.