Excel: The Basics

1. Excel: The Basics

2. Excel: The Basics • Understanding Rows and Columns • Designing Your Spreadsheet with Cells • Naming Worksheets • Using Pattern Fills • Formatting cells • Simple Formulas • Create a Daily Assistance Log

3. Understanding Rows and Columns • Rows run horizontally across the sheet • Row are named by numbers: 1, 2, 3 • Columns run vertically down the sheet • Columns are named by letters: A, B, C • The intersection of a row and column is a cell • Cells are designated by the column then row • Example: C1, D3, F6

4. Columns A, B, C… Cell A1 Rows 1, 2, 3,…

5. Understanding Rows and Columns You can adjust a column width to the width of your text: Type Your Name will go here in Column A The text actually flows over into the column B and C view. To expand Column A to fit the width of the text move your cursor between column A and B in the where the letters of the column names appear. You will see the cursor change into a line with a two-headed arrow. When the cursor changes double-click and the column A will expand to fit the text.

6. Understanding Rows and Columns You can adjust The depth of a row: To expand the depth of row 1 put your cursor between the rows where the numbers appear. You will see the cursor change into a line with a two-headed arrow. When the cursor changes you can drag the row down to adjust the depth of the row.

7. Understanding Rows and Columns You can format an entire Column or Row by selecting the number or letter to select the entire item: Click on the A in Column A and the column will be highlighted. Click on the number 1 in Row 1 and the entire row will be highlighted. Once a column or row is highlighted you can change the font, the alignment and you can format it for color or borders. Right-clickon a selectedrow or column andselect formatcells.

8. Understanding Rows and Columns You can insert rows and columns into your spreadsheet after you have completed it and even after you have begun to enter data. To add a column, select the column in the spreadsheet that you want to be AFTER the new column. Right-click and select insert from the drop down list. A new blank column will be inserted before the column you selected. To add a row, select the row in the spreadsheet that will be BELOW where the new row will be added. Right-click and select insert from the drop down list. A new blank row will be inserted above the row you selected.

9. Designing with Cells You can design your spreadsheet using cells. Put your cursor between row 1 and 2 and drag row 1 down to a height of 42 pixels. Now highlight across 5 columns, A, B, C, D and E. With the 5 columns highlighted select the Merge and Center dropdown and select Merge Cells. The five cells will become one. Type Assistance Log in the cell. Change the Assistance Log text to Arial Bold, 28pt.

10. Designing with Cells Now add some style to your Log: Right-click in the cell next to the word Assistance Log and select Format Cells from the dropdown selection. In the format cells dialog box select the fill tab. Select a color to fill the Assistance Log cell with. Right-click in the Assistance Log cell again and select border. In the style area select a line type, and under border select the third select on the left. This is a border at the bottom of the cell. Then click OK. When you click anywhere outside of the Assistance Log cell, you will now see that your spreadsheet has a header with color and a border to separate it from the data.

11. Designing with Cells Now add categories to your Log: In the row below the Assistance Log cell, enter your categories of data you wish to capture. In this example we enter Name, Dept., Phone Ext., Date and Type of Assistance. Once you enter the categories you will need to expand the cells to fit the information you typed by putting your cursor between the column letters and when it becomes a double headed arrow, double-click. Now select all of the cells in row two that you entered data into, right-click and select a border style and select bottom for the location. Click OK.

12. Naming Worksheets Now that we have the basic shell of your assistance log designed we need to name this worksheet so that you will have a new worksheet for each year. At the bottom of the spreadsheet you will see three tabs. Right-click on the tab named Sheet 1 and select Rename from the drop down list. Name the sheet Log_FY14 At the end of the fiscal year you will rename Sheet 2 as Log_FY15, etc.

13. Copying Worksheets To copy your assistance log header from one year to the next: Highlight just the cells that you want to capture. In this example we highlight the Assistance Log cell and the Name, Dept., Phone Ext., Date and Type of Assistance cells. Then click on copy in the ribbon. Select the sheet named Log_FY15. Select the Sheet selection button. It reside at the intersection of A and 1. When you click it, it will select the entire spreadsheet. The click paste to paste the spreadsheet contents. You will need to readjust your cells again.

14. Pattern Fills Excel has some very useful pattern fills you should be aware of: Type Monday, Tuesday in consecutive cells in a row. Then highlight across the cells that contain Monday and Tuesday. Once both cells are highlighted click the little black square at the lower right corner and holding it drag across 5 more cells. Excel will recognize the pattern and complete the week for you.

15. Pattern Fills Pattern fills work for several scenarios: Type the first two months of the year and then highlight, select the fill square and drag over 10 more cells and Excel will complete the months of the year. Excel will recognize some number patterns. If you enter 1, 1,5, 2 and 2.5 then highlight those cells and drag the fill square it will continue with the pattern. Pattern fills also work for filling in a whole column with the same information. Perhaps my spreadsheet needs to indicate that everyone is an “end-user” status. But you do not want to have to type end-user over and over. You can type it once, then highlight that cell, select the pattern fill square and drag to fill as many cells as you like with “end-user”.

16. Formatting Cells Numbers can be formatted in many ways with Excel: Using the example spreadsheet, the Practice with Numbers sheet has three columns of numbers. Highlight the first column of numbers by selecting the column header A, and right-click to select format cells. In the format cells dialog box select Currency. Select 2 decimal points and the $ for the symbol. Click OK and the numbers will be reformatted to currency with a dollar sign.

17. Simple Formulas Excel can accurately sum figures for you. Enter a column or row or numbers. Highlight the numbers then highlight additionally one blank cell for the sum to appear in. Click the AutoSum button at the upper right of the ribbon and the numbers will be summed.

18. Simple Formulas Excel can also easily average numbers Using the same numbers that you just summed, highlight the numbers and select additionally one blank cell and select Average from the Sum drop down menu. You will then see the average.