Microsoft Excel 2013 through GCFLearnFree.org GCFLearnFree.org offers thousands of free online lessons and tutorials in Reading, Math, Technology, and Job Skills. Recognizing that everyone learns differently, their lessons and tutorials often come in multiple forms (i.e. text, audio and video). We suggest using many of their Microsoft Excel 2013 tutorials that can be found at http://www.gcflearnfree.org/office2013/excel2013

GCFLearnFree Screenshot, Pilmer, 2015, http://www.gcflearnfree.org/office2013/excel2013

We have provided brief descriptions of each of the tutorials in the tables on the accompanying pages so that you can quickly find the information that is relevant to you.

NSSAL © 2015

1

Draft C. D. Pilmer

Tutorial 4. Getting Started with Excel

Information Supplied  Excel 2013 is a spreadsheet program that allows you to store, organize and analyze information, particularly numerical information.  It can be used to create budgets, invoices, contact lists, and a variety of graphical representations (e.g. circle graphs).  For Excel, like MS Word, - The ribbon at the top of the screen is a collection of tools and features available in 2013. - The ribbon is divided into tabs like Home, Insert, and Page Layout. - There are commands under each tab which have been organized into groups. For example under the Home tab, we have groups like Clipboard, Font and Alignment. - Some groups have an arrow in the right hand corner so that you can access additional commands. - The backstage view of your document can be accessed by clicking on the File tab. This allows you to access commands like New, Open, Save, and Print.  The quick access toolbar in the upper left hand corner allows you to access frequently used commands like Save and Undo.  Every spreadsheet contains cells, columns and rows for entering data, plus the formula bar for working with your data.  Scroll bars on the bottom and right allow you to view different areas of your spreadsheet.  In the bottom right hand corner, you can quickly adjust your view of the document by clicking and sliding the Zoom control. In this same area, you can access Page Layout so that you can see what the spreadsheet will look like on a printed page. It also allows you to insert headers and footers while in this viewing option. -

5. Creating and Opening Workbooks

 For Excel, like MS Word, - To create a new spreadsheet, click on the File tab so that you are in the backstage view and then select New. Most learners then choose Blank Workbook; as opposed to other template options. - To open an existing spreadsheet, click on the File tab and select Open. Documents that you have been working on over the last few days can be found by clicking on Recent Workbooks. Older documents likely have to be accessed by clicking on Computer and navigating through the files on your hard drive.

6. Saving and Sharing Workbooks

 It is important to save your document regularly in case your computer shuts down unexpectedly.  For Excel, like MS Word, - The quickest way to save a document is to click on the save icon (looks like a floppy disk) in the upper left hand corner above the ribbon. If it is a new document, then it will take you to the backstage view where you will have to click on Save As and specify where you want to store the document; most learners will choose Computer. You may have to change the default settings from Skydrive to Computer by using the Options in the backstage view, selecting Save, and then check the box that says Save to Computer by default. - A backup copy of your document will be saved as a result of the AutoRecover feature in Excel. The recovered document will show up in the Document Recovery panel the next time Excel is opened. - To export a file to an alternative file type (e.g. PDF), click on the File tab, select Export, and then select the desired format.

NSSAL © 2015

2

Draft C. D. Pilmer

Tutorial 7. Cell Basics

Information Supplied  Every cell has an address (e.g. E3: The cell in column E and row 3). The cell address will appear in the name box above the spreadsheet.  To select multiple cells, click and drag such that the desired range is highlighted.  Cells can contain text, numbers, formulas, functions, and formatting elements.  Contents in a particular row can be eliminated by selecting the row, clicking on the Clear command in the ribbon, and choosing Clear Contents. You can also use Backspace or Delete on your keyboard; most learners choose this technique.  If you wished to delete the cells, as opposed to clearing the content from the cells, select the cells in that row, and select the Delete command under the Home tab and Cells group.  Content in one cell can be copied to another cell or multiple cells using Copy and Paste commands under the Home tab and Clipboard group. Right-clicking after selecting a cell or cells will also allow you to access these clipboard options.  Cells can be moved from one location to another using the drag-and-drop technique.  Cells can be filled vertically or horizontally by selecting the initial cell and dragging the small square in the lower right-hand corner (i.e. the fill handle) vertically or horizontally.  The Find feature, found under the Home tab and Editing group, can be used to locate specific information in your spreadsheet.  The Replace feature, found under the Home tab and Editing group, can be used to replace old information with newer information in multiple cells of your spreadsheet.

8. Modifying Columns, Rows and Cells

 You often have to change the size and layout of your cells to better fit the information you are working with.  Columns can be widened by placing the cursor over the right line in the column heading and dragging it to the right. Another option is to autosize the column by double clicking on the right line; as opposed to, dragging. Rows can be altered in the same manner.  To insert a new row into an existing spreadsheet, select the row heading below the one to appear and click on the appropriate Insert command under the Home tab and Cells group. Columns are inserted in a similar manner.  To delete a column or row, select the column or row and click on the appropriate Delete command under the Home tab and Cells group.  Rows and columns can be moved using the Cut and Paste commands under the Home tab and Clipboard group.  Columns or rows can be temporarily be hidden by selecting the columns and/or rows, right-clicking, and selecting the Hide option. To make them reappear, select the columns to the right and left of the hidden materials, right-click, and select the Unhide option.  Wrapping text is one way to deal with cells that contain more text than they can appropriately display. The Wrap Text command under the Home tab and Alignment group allows one to display text within multiple lines within a cell.  Cells can be merged using the Merge & Center command under the Home tab and Alignment group.

NSSAL © 2015

3

Draft C. D. Pilmer

Tutorial 9. Formatting Cells

Information Supplied  Formatting can make the information easier to read and visually more appealing.  Font size, font style, font color, and cell fill color can be adjusted within one cell or multiple cells using a variety of commands in the Font group under the Home tab.  The Cell Styles commands can be used to apply different formatting to all or portions of your spreadsheet.  A variety of cell border options are available using the Borders commands found under Home tab and Font group.  Information within a cell or cells can be aligned using a variety of options within the Alignment group under the Home tab.  Numbers and dates can be changed to different formats by using the options from the drop-down menu within the Number group under the Home tab.

MS Excel Assignment #1 This assignment comes in two parts. For the first part, you will recreate the spreadsheet below that contains customer information for a fictitious company. (Font: Arial, size 10 and 20) You will have to merge cells, change fonts, adjust cell sizes, use text wrapping options, and select the most appropriate border, alignment, and formatting options. This recreated spreadsheet will be submitted to your instructor in both electronic and hardcopy form.

In the second part of the assignment, you will make a copy of the first excel file, and then alter that copy slightly. You will:  Eliminate the row for the third customer in the spreadsheet.  Insert a new row somewhere into the middle of the spreadsheet containing information for a fictitious customer of your choosing.  Insert a new column to incorporate new and fictitious customer information of your choosing.  Use the Replace command to change the word “weekly” to “monthly” throughout the spreadsheet. Again, submit both an electronic copy and hardcopy of this revised spreadsheet to your instructor. NSSAL © 2015

4

Draft C. D. Pilmer

Let us look at other GCFLearnFree videos. Tutorial 10. Worksheet Basics

Information Supplied  Multiple spreadsheets can be imbedded into one file using the New Sheet button (looks like a plus sign in a circle) found to the right of your tabs.  You can copy and existing spreadsheet to make a new spreadsheet within the same file. Choose the sheet you wish to copy, right-click, in the window that appears click on Create a copy, identify where you want the copy to go, and click on OK. The new sheet will be named as a copy of the original sheet. To rename the new sheet, right-click on the tab, select Rename, and enter the desired name.  If you wish to reorder your spreadsheets within a single file, merely drag-anddrop the spreadsheet tabs to their desired locations.  The same modification can occur to multiple spreadsheets within a file by first grouping the sheets. This is done by selecting the first spreadsheet tab, then pressing and holding Ctrl on your keyboard while drag your mouse over the other tabs which you wish to group with the first spreadsheet that you selected. When the sheets are grouped, a change made to one will result in an automatic change to the others. To ungroup the spreadsheets, right-click on one of the grouped spreadsheet tabs, and select Ungroup.

11. Page Layout

 The Page Layout view in the bottom right hand corner will help you visualize your final product.  The page orientation can either be portrait (i.e. vertical) or landscape (i.e. horizontal). The orientation can be changed by clicking on the Page Layout tab, and selecting the desired orientation from the Orientation command in the Page Setup group.  The margins of a document can be adjusted using the Margin commands found under the Page Layout tab in Page Setup group. Most learners find it easiest to use the Custom Margins option.  While in the Page Layout view, a header and/or footer can be added to your spreadsheet by clicking in the designated areas above and below the spreadsheet. When you click in those areas, the Design tab will open. That gives you access to commands that allow you to add page numbers, dates, and a host of other features to your header or footer.

12. Printing Workbooks

 Everything you need to know about printing spreadsheets can be found in the backstage view under Print.  If your file has multiple sheets, you have to decide whether you wish to all of them or selected sheets. Under Settings, you can schoose from Print Active Sheets, Print Entire Workbook, and Print Selection. The Print Selection command allows you to print only selected portions of a spreadsheet.  Sometimes when a spreadsheet is printed in its original size, rows and/or columns are cut off (They will appear on separate piece of paper.). To correct this problem, use one of the Scaling options available in the last drop-down menu for Settings.  Another way to make a spreadsheet fit the paper is to adjust the margins on the document. This can be done in the accessing the Margins options under Settings. Most people choose the Custom Margins option or manually adjust the margin lines after activating the Show Margins button in the lower righthand corner.

NSSAL © 2015

5

Draft C. D. Pilmer

Tutorial 13. Simple Formulas

Information Supplied  In Excel, the asterisk symbol (*) is used for multiplication, the forward slash (/) is used for division, and a caret (^) for exponents.  Start all formulas with an equal sign (=).  Cell addresses (e.g. C3) are needed when constructing formulas.  Suppose you wanted to add the numbers in cells B2 and B3, and display that answer in cell B4. You would click in cell B4 and enter = B2 + B3. Once you press Enter on the keyboard, that desired sum will appear in cell B4.  Suppose you wanted to multiply the numbers in cells E4 and F4, and display that answer in cell G4. You would click in cell G4 and enter = E4 * F4. Once you press Enter on the keyboard, that desired product will appear in cell G4.  If you make a mistake with your formula and don’t realize it until after pressing Enter, just double-click on the cell and the formula will reappear so that you can make the necessary changes.  Instead of typing similar formulas over and over again within the same spreadsheet, the fill handle (little square in the lower right-hand corner of a selected cell) can be dragged over multiple cells where you wish the new formulas to appear.

14. Complex Formulas

 Sometimes you need to create formulas that contain more than one operation. For example, the formula ( B3 + B4 + B5 ) * D7 involves the operations of addition and multiplication.  Excel uses Order of Operations (i.e. BEDMAS) when calculating answers. You must understand Operation of Operations if you wish to make formulas that generate the desired answers. Bracket (or parentheses) are often the best way of controlling which parts of the formula are calculated first.

15. Relative and Absolute Cell References

 Using the fill handle to make multiple similar formulas is very useful. For example if the formula for the original cell is E3 + F3 + G3, then fill handle can be used to generate formulas like E4 + F4 + G4 , E5 + F5 + G5, E6 + F6 + G6, etc. Notice, that in this example, that all the addresses in all the formula are changing, and in the same manner.  Sometimes when we are using the fill handle to generate multiple formula, we want certain addresses in the formula to remain the same throughout. To do this we create an absolute cell reference in our formula. This is done by using the $ symbol within the address. For example if the formula for the original cell is E3 + F3 + $G$3, then fill handle can be used to generate formulas like E4 + F4 + $G$3 , E5 + F5 + $G$3, E6 + F6 + $G$3, etc. Notice that in this example one address in all the formula does not change. The numerical value in cell G3 is used in every formula.

16. Functions

 Functions, which are found in the Function Library group under the Formulas tab, are often used when writing out a formula takes too long and is too cumbersome.  The functions that you will use most often are Sum and Average.  Suppose we wanted to add all the numbers in a particular column. Click on the cell where you would like the answer to appear. Then select Sum from the AutoSum drop-down menu which is under the Formula tab and Function Library group. Excel will select the cells that it assumes you wish to add. If Excels assumptions are wrong, you can drag the corners of the selected cells to the desired locations. After you press Enter on your keyboard, the sum will appear in your desired cell. If you double-click, the formula will appear (e.g. =SUM(D2:D10) ).  Functions, like formulas, can be repeated using the fill handle.

NSSAL © 2015

6

Draft C. D. Pilmer

MS Excel Assignment #2 Randy has six part-time employees working for him. He has recorded information regarding a two week pay period for these employees in a partially completed spreadsheet.

Do the following. (a) Reproduce the partially completed spreadsheet. (Font: Calibri, Font Size: 11) (b) Create a header which states that this is for the Pay Period from May 1 to May 14. (c) Create formulas that allow you to work out how much each part-time employee earned (before deductions) based on the number of hours worked and their hourly wage. (d) Use the Sum function to work out the total of hours worked by all six employees, and the total earnings of these employees. (e) Use a copy of this spreadsheet to create a second spreadsheet within this same file that will represent the second two week pay period. Change the pay period in the header and some of the numbers in the spreadsheet to values of your own choosing. Submit both an electronic copy and hardcopy of these spreadsheets to your instructor.

Let us look at other GCFLearnFree videos. Tutorial 17. Freezing Panes and View Options

Information Supplied  With large spreadsheets, you may wish to see something in the first column and simultaneously see things in the last few columns, but cannot do so because the sheet is too wide. To solve this problem, you can use one of the Freeze Pane options so that the first column is always visible when you scroll to the right on your spread sheet. These options are found under the View tab and in the Window group. (The other content found in this video is beyond the scope of this course.)

18. Sorting Data

 In Excel, information can be sorted alphabetically, numerically, by date, and by color.  Sorting can be done using commands under the Home tab or Data tab.  Most people find it easiest to select the cells prior to selecting the Sort option. If not, Excel will make assumptions as to which cells need to be sorted and as a result can make errors on occasion. (The other content found in this video is beyond the scope of this course.)

NSSAL © 2015

7

Draft C. D. Pilmer

Tutorial 19. Filtering Data

Information Supplied  Sometimes you have spreadsheet that has more information than you need at the time. You don’t want to eliminate the information you don’t presently need, but you want to temporarily hide it so that you can focus on the relevant information. This is done by filtering the data.  You must have a header row (i.e. a row describing the columns below) if you wish to use the filtering features. To filter, select the header row, go to the Data tab and choose the Filter command. You will notice that little arrows appear in the lower right-hand corner of the cells in your header row. These arrows give you access to all of the filtering options.  Filters can simultaneously be added to multiple columns, thus hiding large portions of the original spreadsheet.  To remove filtering, select Clear Filter from the filtering options. (The other content found in this video is beyond the scope of this course.)

21. Tables

 You may wish to format all or a portion of spreadsheet using a variety of preset formatting options. Select the portion of a spreadsheet you wish to format, click on the Format as Table drop-down menu under the Home tab and Styles group, and then choose the style you prefer. (The other content found in this video is beyond the scope of this course.)

MS Excel Assignment #3 Your instructor will supply you with an electronic copy of a spreadsheet. This spreadsheet contains employee information for a fictitious company.

You must do the following to the spreadsheet. (a) Add two fictitious employees of your choosing to the spreadsheet. Also fill in the cells in these rows with the relevant information, again, of your choosing. (b) Use the Format as Table drop-down menu to format the table in a style that you prefer. (c) Using the Sort options, list the employee names in alphabetical order. (d) Freeze the first column so that the employee names are always visible when you scroll to the right, regardless of the window size. (e) Print the spreadsheet such that it fits on one sheet of paper. This means you will either have to use the Scaling options or Margin options available in the backstage view. (f) Now use the filter options to only display the full-time employee information. Print this spreadsheet such that it fits on one sheet of paper. Give your instructor the electronic copy of your file and the two printed spreadsheets. NSSAL © 2015

8

Draft C. D. Pilmer

Let us look at other GCFLearnFree videos. Tutorial 22. Charts

Information Supplied  When working with numerical data in Excel, charts (or graphs) are often the best way to show the meaning behind or trends within those numbers.  We have can choose from column charts (e.g. bar graphs) and line charts (i.e. line graphs), pie charts (i.e. circle graphs), bar charts (i.e. column charts turned on their side) and area charts. We will not be using area charts in this course.  To create a chart, select your data, including the headers you want to use. Then go to the Insert tab and look at the commands in the Charts group. Choose the chart type that you feel is appropriate for the data.  You may wish to group your columns in a different manner. This can be done using the Switch Row/Column command under the Design tab.  If you want to change the chart type, use the Change Chart Type command under the Design tab.  The Quick Layout drop-down menu under the Design tab and Chart Layout group allows you to quickly change the layout of your chart to some of the most common and popular layouts.  You can add or remove chart elements (e.g. grid lines, legend, table) by clicking on the “plus sign” (+) to the right of your chart.

MS Excel Assignment #4 Part 1 You are supplied with a spreadsheet that shows the sales, in thousands of dollars, for six departments in a department store over the four seasons of the year.

(a) Reproduce this spread sheet. (Font: Times New Roman, Font Size: 10) (b) Create a column chart where the four seasons are along the horizontal axis. Part 2 Use the internet to find some real data that would display nicely in a pie chart (e.g. the areas of employment for registered nurses in Canada). Indicate when and where you obtained this data. Construct a spreadsheet and pie chart in Excel using that data. Give your instructor the electronic copy of both of these files.

All images of Microsoft Excel icons, drop-down menus, and commands found in this document were screen captures directly from the Excel application. These screen captures were done by Dave Pilmer in 2015.

NSSAL © 2015

9

Draft C. D. Pilmer