Getting Down To Work - Microsoft Excel

1. Excel®

Excel® is a spreadsheet program that allows you to enter numbers or data into columns and rows.  Once the data is entered you can make calculations from simple mathematical equations to intricate data summaries. 

Excel® is complex and mastery of this software program often takes years of practice.  We will review some of the basic functions and the simplest calculations.  Teachers often use Excel® to keep track of grades, feedback and survey data.

To begin, click on Excel® and open a new workbook.  Similar to Microsoft Word ®, you open the workbook by going to the File section, selecting New and then Blank workbook.  The pages of the workbook are referred to as spreadsheets. 

We will work with a single spreadsheet. 

The spreadsheet contains a series of boxes called cells.  Each cell is a separate piece of data. 

Cells can be moved around and combined.

1.1. Blank Workbook View

1.2. Toolbar

Excel® has a toolbar with tabs that are similar to those in Microsoft Word ®.  However, Excel® is different in that many of the standard actions you will use are contained within the spreadsheet itself, with options available by right-click.  As you work with more complex data, you will use the toolbar more. 

We will look at the options in the most-used sections of the toolbar and then discuss actions in the spreadsheet itself.

1.3. Toolbar View

1.4. Home Bar

The Home tab contains the text editing options:  font, colour, spacing etc. 

In the Alignment section of the Home tab, you will see an option to wrap text.  This is useful if you are entering several words into a single cell but don’t want the column to get too wide. 

You can right click on a cell in the worksheet to see many of these options as well. 

1.5. Home Bar View

1.6. Formulas Bar

The Formulas tab is where you will find formulas and functions to assist you with your calculations.

The option called AutoSum is a shortcut to easily add a column or row of numbers. 

On the spreadsheet, start with the last number in the list and click on it to highlight it.

Continue to hold down your mouse button and drag the cursor up or across to highlight all the numbers you want to include in the calculation. 

Once they are highlighted, click on AutoSum and the total will automatically appear in the next cell.  

1.7. AutoSum View

1.8. Working with AutoSum

Look at the bar just below the toolbar.

This is where you will see the cell reference and the actual formula that was automatically created to complete the calculation. 

Looking at these AutoSum formulas is a good way to start learning how to write your own formulas.

If you want to do another simple calculation, do the same selection of numbers and then click on the small arrow below the AutoSum to reveal options such as find the Average or find the Minimum number.

Tip: if you get messed up making a calculation and need to re-do the function, hit the Escape key on your computer.

1.9. Formulas View

1.10. Entering Data

To enter data into the spreadsheet, click on an empty cell.  Each cell has a letter and number reference.  For example, cell A1 is in the top left hand corner of the spreadsheet.  Cell B1 is directly to the right of A1. Cell A2 is directly below cell A1. Press enter to move to the next cell.

Data can be either numbers or words.  If you know you are going to calculate the numbers, make sure they are in a separate column from the words.

You can change the size of the columns or rows by clicking on the gridlines and dragging them to the desired size.

1.11. Cell Numbers View

1.12. Formulas

When you need to do a calculation that is different from the AutoSum options, you can create your own formula directly in a cell on the spreadsheet.  

Choose a cell where you would like to display the result of the calculation and click there to begin writing your formula.
The symbols used to write a calculation are similar to those you may have used on a calculator.

Add                    +

Subtract            -

Multiply           x

Divide              \

The difference is that you place the = (equal sign) at the start of the formula.  That symbol tells Excel® that this cell will contain a formula.  

For example, if you wanted to subtract the number in cell C2 from the number in cell C5, you would type =(C5-C2).  Then hit Enter to run the calculation and see your result. 

While you are typing your formula, a little pop-up box may appear which is trying to guess what you are doing.   Since it assumes more complex calculations, you can ignore it and continue to enter your formula.  If you have written the formula incorrectly (e.g. added a space or left out a bracket), a second pop-up may appear that will attempt to auto-correct the formula.

1.13. Formula View

1.14. Column Headers

If you are planning to convert your spreadsheet to a table you may want headers at the top of your columns. 

Add this header text to the cells in row 1 or leave that row blank.  It is easier to do this at the beginning than trying to add this information after you have completed your data entry. 

It is possible to add rows and columns if you need to do so after you have entered data.  Right click on a cell and go to Insert for options.

1.15. Moving Rows & Columns

Sometimes you need to move a row or column that already contains data.

Click on the row or column heading and the entire row or column will highlight.
Move your cursor to the border of the row or column.  When the cursor changes to a move pointer,   you can drag the row or column and all of its contents to a new place in the spreadsheet.

Be careful not to overwrite content.  For example, if you wanted to move the data in column A so that it now sits betwen columns B and C, you will need to Insert a new column there first.  Do this by clicking in the column heading next to where you want the new column to appear and then right-click and choose Insert.

You can also select portions of rows or columns or individual cells to move using the same method. 

1.16. Tables

When you have your data entered and your formulas complete, you can select everything and put it into a table. 

This will allow some easy ways to further manipulate your data (filter, sort). This is useful when you have long lists of data that you may have been entering over a period of time.

Select all your data by clicking the last cell or the first cell and dragging diagonally until the entire area is highlighted. 

Click on the Insert Bar at the top of the screen and locate the Table icon.

Click on Table. 

Excel® will ask you to confirm your selection. 

If you have included headers, there is a checkbox to indicate this.  

1.17. Create Table View

1.18. Sorting

Once you have selected a Table, a new section called Table Tools will appear in the top toolbar. 

On the spreadsheet, you will now see small arrows under each heading in your table. 

These will allow you to sort the data in a variety of ways such as: alphabetizing; or, sorting from largest number to smallest number.

1.19. Sort Table View

1.20. Close Table

When you have finished your sorting, you can return to the regular spreadsheet view by selecting Convert to Range in the Table Design Toolbar.

1.21. File Tab

The File tab opens in the same way it does in Word ®.  This is where you can Save or Print your spreadsheet or Open a new workbook.  In Excel® 2013, the file tab will open to the Info page that gives you some options of how to check and protect your work.  Use the back arrow at the top of the column to return to your spreadsheet.

Saving your spreadsheet is the same as saving a Word ® document.  From the File tab, select Save As and name your document or if you have already saved it before, select Save.  This will save the entire workbook.

1.22. File Tab View

1.23. Quick Review Activity

In this section of the course, you have learned to:

• Create and save a basic Excel® workbook. 

To test your knowledge, try to complete this activity:

1. Open a blank workbook. 
2. Create a spreadsheet with three columns and four rows.
3. Put random numbers into each of the cells in the spreadseet.
4. Add all the numbers in column two and show the total in a cell directly underneath.
   
5. Close Excel®.  Because this is an activity, you do not need to save the workbook file.
   

Can you answer these questions?

• Where in the spreadsheet view do you see the formula?
  
• What is the purpose of putting your data into the Table format?