Welcome To Excel 2007

Welcome to Excel 2007 The New Interface Microsoft rethought the way users interact with their software with all of the Office 2007 applications. Eliminating the standard menus and introducing a new ribbon interface in some ways simplifies using Microsoft Excel 2007 and in other ways makes it much more difficult to use the application. Before, there were several ways to perform a function in Excel 2003 and older; now, there is typical one way to perform a function – finding it and remembering how to do it creates a learning curve you have to overcome with Excel 2007. Microsoft has a different viewpoint on this topic and believes the learning curve is very minimal for the Office 2007 suite of applications – from my experience with multiple clients converting to Office 2007 (not to mention our own personal learning curve with Excel 2007), most users can take six months before they feel comfortable with the new interface. We still find ourselves hunting for commands! Overall, we like the new interface and are glad Microsoft decided to change directions.

The Office Button The Office Button, , opens a menu with many of the functions previously available on the File menu in older versions of Office.

From this menu, you can create a new document, open an existing document, save the document, print it, prepare it for distribution, send it, publish it to the web, close the document, access Excel 2007 options, or exit Excel. Additionally, you can access recent documents in this menu.

As you navigate the menu options, the Recent Documents pane changes with options for Save As, Print, Prepare, Send, and Publish. Each of these options has several sub-options.

For example, when you select Save As, the pane on the right changes to options available for that menu option

Excel 2007 Ribbons

Excel 2007 has eight standard ribbon tabs (nine if you count Developer – shown in the image above; has to be turned on by the user) and an occasional “contextual” tab that shows up when you have a certain item selected. For example, if you have a picture selected, a Picture Tools: Format ribbon is available, as show in the figure below.

Home Ribbon

The Home ribbon has common formatting and edit commands. Insert Ribbon

The Insert ribbon allows you to insert common objects, charts, links, images, and more. Page Layout Ribbon

The Page Layout ribbon is used to layout your spreadsheets for printing. Formulas Ribbon

The Formulas ribbon allows access to different formulas so you don’t have to memorize all of the functions. Data Ribbon

The Data ribbon has commands for accessing external data, sorting and filtering, and managing data in the spreadsheet. Review Ribbon

The Review ribbon contains the proofing and reviewing tools. If you have a tablet PC, you can also mark up the spreadsheet with “Ink.” View Ribbon

The View ribbon has different views available, allows you to control the zoom view of the document, and lets you access Macros. Developer Ribbon

If you are an Office developer, you may want to turn on the Developer ribbon. The Developer ribbon is used to access the underlying code and projects for writing modules for Microsoft Excel 2007. How to Turn on the Developer Ribbon 1. Click on the Office button. 2. Click on Excel Options.

3. Check Show Developer tab in the Ribbon. 4. Click OK. Add-Ins Ribbon If you have add-ins installed in Microsoft Excel 2007, you will see an Add-ins ribbon which contains the functions available to the add-in. Accessing the Ribbons Using the Keyboard Each of the ribbons has a keyboard shortcut to access it. Press and hold the Alt key on your keyboard to see the key combination:

Alt+H activates the Home ribbon, Alt+N the Insert, and so on. You will notice that Alt+1 activates the first button on the Quick Access Toolbar at the top . After you activate a Ribbon, hold down the Alt key again and you will see the key combination to activate certain commands in the ribbon:

To access the second level of keyboard shortcuts, you need to make sure you press the key combination for the ribbon. For example, you insert a function, you press Alt+M to access the Formulas ribbon, then Alt+F to Insert a Function. You will see the keyboard combinations appear as you hold down the Alt key to see which level you are accessing.

Office 2007 File Formats Microsoft changes the file format in Office 2007, moving to an XML based format. Each of the previously recognizable formats (e.g. doc, ppt, xls, mdb) has been changed with an “x” on the end, e.g. docx, xlsx, pptx, mdbx. This change means users of older versions of Office cannot natively open the new formats, though Office 2007 is backwardly compatible with previous versions for both opening and savings to the old file formats. When you save to the older file formats, certain objects only available in the new version are not compatible and are saved into a

flat version which makes them no longer editable. By default, you will be notified when items in your document are not compatible.

Change Default Save Filetype If you are in a mixed environment with a lot of Office 2003 users, it may make sense to change your default filetype to Office 2003. 1. Click on the Office button. 2. Click on Excel Options. 3. Click on the Save tab on the left.

4. Click on the pull-down menu Save files in this format. 5. Select the default format you want to save Excel spreadsheets in. Select Excel 97-2003 Workbook (*.xls) for the most compatibility with older versions. 6. Click OK to save the settings.

Creating Workbooks When you open Microsoft Excel 2007, it opens a new workbook. If you are working in Excel and would like to create a new workbook, click the Office button. Select New on the Office menu.

The New Workbook dialog box opens. In the box, you can create a blank workbook, or choose from many templates available installed locally and on Office online. You can find hundreds of templates to begin your spreadsheet, or just start with a blank canvas and create your own. If you wanted to create a budget, you can click on the Budgets category to see dozens of templates with many of the categories to create your own personal budget

Quick Access Toolbar At the top of the Excel 2007 window is the Quick Access Toolbar.

The Quick Access Toolbar has frequently used tools which are available no matter which ribbon tab you are in. Customizing the Quick Access Toolbar 1. Click the

customize button on the right of the QAT.

2. Select options you would like to add to your QAT. 3. Once you select the command to add, it appears immediately.

4. In this example, we added the Quick Print button.

Customizing the Interface One of the nice things in Microsoft Office is the capability to customize the interface. In the previous section, you learned how to customize the Quick Access Toolbar. In this section, we will explore the Excel Options dialog box and the different areas you can change to customize Microsoft Excel 2007. Customizing Excel 2007 1. Click on the Office button. 2. Click on Excel Options.

3. The Popular Options are the ones most frequently changed. The first option, Show Mini Toolbar on selection, controls the display of a context sensitive popup menu on the Excel spreadsheet which has formatting controls:

Turning this option off will disable this popup menu. 4. Live Preview is an option which will display what a formatting change will look like to a cell without actually changing the cell formatting. For example, if you select a cell, then run your mouse over a color, Live Preview shows you how the cell would look with that change. 5. You can also change default formatting preferences for new workbooks on this screen.

6. The Formulas tab has customization options for formulas, calculation options, and error checking. 7. The Proofing tab contains the options for Autocorrect and spelling.

8. The Advanced tab contains many of the options necessary to fully customize Excel. We recommend perusing all of these options to get Excel 2007 how you like it – especially if you come from a different spreadsheet application such as Lotus 1-2-3.

9. The Customize tab allows you to customize the Quick Access Toolbar (a different method of customization was shown in the previous section). Minimizing the Excel 2007 Ribbon Microsoft provides the ability to minimize the ribbon if you are short on screen space. 1. Right-click on any of the boxes in the ribbon.

2. Select Minimize the Ribbon. 3. The ribbon names will still appear and as you click on the name, the ribbon will appear Showing a Dialog Box of Options

On some of the sections on the ribbons, you will see an icon which opens a dialog box of options. For example, if you click on the in the Page Setup section of the Page Layout ribbon, you will open the Page Setup dialog box. Opening the Page Setup Dialog Box

1. Click on the

icon in the Page Setup section.

2. The Page Setup dialog box opens. You can use these instructions to access many of the dialog boxes you are familiar with from Office 2003 and earlier

Saving Spreadsheets There are several ways to save your spreadsheet. The first is to use the Quick Access Toolbar Save button: in the upper left hand corner next to the Office button. The second way to save is to use the old key combinations: CTRL+S or Alt+F S. Finally, you can save the document by clicking on the Office button. Saving a Spreadsheet 1. Click the Office button.

2. Click on Save.

3. Select a location to save your file and name the spreadsheet. 4. Click Save. After you save the spreadsheet the first time, clicking the save button or selecting Save on the Office menu will overwrite the previously saved version. If you want to save the file with a different name or format, follow the “Save As” instructions below. How to Save the Document in a Different Format 1. Click the Office button. 2. Hover over (don’t click yet) Save As.

3. Select a format you’d like to save the file in, or just click on Save As to select the format in the Save As dialog box.

4. Navigate to the folder you’d like to save the file in and name it. 5. Select a file type in the Save as type pull-down menu.

6. Click Save. Importing and Exporting Data One of the powerful features of Microsoft Excel is the ability to import and export data. You could have data in a different file format or an export from your corporate accounting system and want to analyze the data – Excel can import from dozens of formats. In a later tutorial, we explore importing data in a business context. First, we’ll give you the generic steps required to import data. There are two methods to import data: from an existing file or from a data source. Importing Data from an Existing File 1. Click the Office button. 2. Click the Open button.

3. Navigate to the folder containing the file you want to open.

4. Select the file. If you don’t see the file you want to open, pull down the All Excel Files menu to the right and select the format the file is in. Once you see the file, select it. 5. Click Open. You should now see the file:

Working with Data & Formatting Your Spreadsheet The Family Budget As a spreadsheet application, Excel is great at analyzing a lot of data. It is useful for financial information, data from corporate systems, or just balancing your checkbook. Though it is great at these functions, it is not necessarily great at being a word processing system or a data storage function for your address book or corporate IT helpdesk. These functions are better served by Microsoft Word and Microsoft Access respectively – though I have known quite a few accounting folk who use Excel for everything! We have explored a lot of the interface and working with the Excel application in previous sections – now we’re going to look at how to work with the program and work with data. We’re going to accomplish this by creating a budget. Throughout the tutorial, you will find downloadable files with where we’re at in the tutorial in case you want to update with a fresh copy that we created or in case you want to skip ahead.

Creating a New Workbook The first step in creating the budget is to open a new workbook. If you don’t have Excel 2007 open yet, open it now. If you do have it open, let’s create a new workbook. Creating a New Workbook 1. Click on the Office button. 2. Click New.

3. Select the Blank and recent templates. Select Blank Workbook. 4. Click Create. Timesaver: You can also skip those four steps and press CTRL+N to create New workbook. Next, we are going to save the workbook so we don’t lose any data. Excel has a great feature where it frequently saves a temporary file of your data so if it or your computer crashes, it can recover data you entered after your last save. Saving Your Workbook 1. Click the Office button. 2. Click Save.

3. Navigate to the folder you want to save your budget in. 4. Name the file Family Budget.xlsx. 5. Click Save.

Now that we have saved the file, let’s start entering data in the spreadsheet Entering and Revising Data Understanding Cell References

Excel references cells in the grid by a letter code on the horizontal (displayed at the top) and a number code on the vertical (at the left). To reference a specific cell, you specify the letter first and the number second, for example, the top most left cell is A1. A2 is one cell down, B1 is one cell to the right, and so on. If we specify for you to enter data in B2, you will move to the second cell in the spreadsheet to right and second cell down:

You will also see above the spreadsheet grid at the far left is the reference to the cell you are currently in. Entering Data in Microsoft Excel

1. Go to cell B1. 2. Type in Budget.

3. Press Enter. You should now see a spreadsheet that looks like this:

There are two ways to edit the data in cell B1. You can double-click on the cell and it will let you edit directly in the cell, or you can click on the cell and click on the data in the Function Bar. Editing Data Using the Function Bar 1. Click on cell B1.

2. 3. Click on the word Budget in the Formula Bar. 4. You can now change, edit, or delete the contents of the cell. We are going to leave the cell as it is. We’re creating a family budget, so we need some categories to put income and expenses in. Setting Up Our Budget Categories 1. Click in cell A2. 2. Type in Wages. Press Enter. Don’t worry about formatting right now, we will format the labels and categories later. 3. Type in Interest. Press Enter. 4. Type in Self-Employment. Press Enter.

5. Type in Miscellaneous. Press Enter. 6. Type in Income Total. Press Enter. 7. Your spreadsheet should look like this:

8. You know, I think a better description for cell A3 should be Interest/Dividends instead of just Interest. 9. Click on cell A3. 10. Click again on the word Interest. An edit mark should appear, add /Dividends on the end of the word Interest:

11. Press Enter. 12. Our first categories are now setup. You will notice that several of the labels exceed the width of the column and overflow into the B column. Later, we are going to change the width of the column. 13. Let’s get back to creating our categories. 14. Click on cell A7. Type in Investments and press Enter. 15. Type in Retirement and press Enter. 16. Type in Education and press Enter. 17. Type in Mutual Funds and press Enter. 18. Type in Savings and press Enter.

19. Type in Expenses and press Enter. 20. Type in Housing and press Enter. 21. Type in Mortgage/Rent and press Enter. 22. Type in Maintenance and press Enter. 23. Type in Home Improvement and press Enter. 24. Type in Home Security and press Enter. 25. Type in Property Taxes and press Enter. 26. Type in Homeowner’s Insurance and press Enter. 27. Type in Utilities and press Enter. 28. Type in Water and press Enter. 29. Type in Gas and press Enter. 30. Type in Electric and press Enter. 31. Type in Trash and press Enter. 32. Type in Telephone and press Enter. 33. Type in Cell Phone and press Enter. 34. Type in Cable TV and press Enter. 35. Type in Internet and press Enter. 36. Type in Necessities and press Enter. 37. Type in Groceries and press Enter. 38. Type in Medical and press Enter. 39. Type in Insurance and press Enter. 40. Type in Life Insurance and press Enter. 41. Type in Health Insurance and press Enter.

42. Type in Auto Insurance and press Enter. 43. Type in Long Term Care Insurance and press Enter. 44. Type in Disability Insurance and press Enter. 45. Type in Transportation and press Enter. 46. Type in Car Loan #1 and press Enter. 47. Type in Car Loan #2 and press Enter. 48. Type in Fuel and press Enter. 49. Type in Parking and press Enter. 50. Type in Service and press Enter. 51. Type in Miscellaneous and press Enter. 52. Type in Charitable Contributions and press Enter. 53. Type in Entertainment and press Enter. 54. Type in Clothing and press Enter. 55. Type in Vacation and press Enter. 56. Type in Gifts and press Enter. 57. Type in Dining Out and press Enter. 58. Type in Children Activities and press Enter. 59. Type in Debt Repayments and press Enter. 60. Type in Credit Card #1 and press Enter. 61. Type in Credit Card #2 and press Enter. 62. Type in Credit Card #3 and press Enter. 63. Type in Credit Card #4 and press Enter. 64. Type in Loan #1 and press Enter.

65. Type in Loan #2 and press Enter. 66. Save your spreadsheet. Whew! That was a lot of typing. At this point, your spreadsheet should look like this attachment: Family Budget 1.xlsx (zipped) Right now, the spreadsheet isn’t in a condition for us to actually enter data yet. We need some basic formatting to make it presentable and easy to enter data. aved the file, let’s start entering data in the spreadsheet.

Basic Formatting Now, still working with Family Budget.xlsx file, let’s start formatting the spreadsheet to make it a little easier to read and work with.

Formatting the Spreadsheet 1. The first thing we need to do is to fix the column width of column A to fit all of the categories. There are several ways to enlarge the column, the first way is to manually resize the column using you mouse. Move your mouse to the right edge line of the A column until the cursor changes into a “resize column” cursor:

2. You will see the current width appear when you click on the right edge. Now click on the edge and drag to the right, you will see the width change. Release the mouse button and your column will resize. 3. An easier way to resize the column automatically to the size of the largest item is to move your mouse to the right edge line of the column label (in this case, the A) and when it turns into a “resize column” cursor, double-click on it. This will resize the column automatically:

4. Now that we have resized the column, let’s start formatting the cells. Our goal is to separate the “title” rows of each section from the labels of the individual budget categories. Let’s start by bolding the title rows. 5. Click on cell A2. 6. Click on the

icon in the Home ribbon or press CTRL+B.

7. Now click on cell A7, the Investments cell and click on the or press CTRL+B.

icon in the Home ribbon

8. Repeat with cells A11, A12, A19, A28, A31, A37, A43, and A51. 9. Now, the A column is a little easier to decipher, but let’s continue formatting to see if we can really make the titles standout. 10. Click on cell A3.

11. 12. In the Alignment section of the Home ribbon, click on the icon to open the Format Cells dialog box.

13. In the Indent section of the Alignment tab, click the up arrow twice to set the value to 2: 14. Click OK.

15. Now, Interest/Dividends should be indented. This is a better method of indenting cells versus just inserting two spaces at the beginning of the cell label. If you want to later change this to an indent of “3” or eliminate the indent entirely, you can do that in one simple move. If you had put spaces in each cell manually, this would have been much more difficult. 16. Now, we want to make each of the budget categories indented. You could manually repeat this process on each cell, or you could select several cells and perform this routine on a multiple cells. We’re going to do it a little differently than either of these methods – we’re going to use the format painter. About the Format Painter The Format Painter is a great tool in Microsoft Office applications which let’s you apply formatting from a source cell to a different cell.

The Format Painter is available on the Home ribbon in the Clipboard section. There are two ways to use it: a single click allows you to paint the format from one cell to another. Double-clicking the Format Painter allows you to apply the source cell formatting to multiple cells. 17. Click on cell A3. 18. Double-click the Format Painter icon in the Clipboard section of the Home ribbon. 19. Click on cells A4 and then on cell A5. 20. Click once on the Format Painter icon to run off format painting.

21. Cells A3, A4, and A5 should now have the same indenting. 22. Now, while in cell A5, double-click the Format Painter icon again.

23. Select cells A8 through A10 by clicking your mouse button and holding the mouse button down in cell A8, then drag down to cell A10. Cells A8-A10 will highlight. Release the mouse button and they will be formatted. 24. If at any time you are applying formatting you lose the ability to paint format, click in any cell which is already formatting with indentation and double-click on the Format Painter icon. It’s important to note that whatever cell you are in when you click on the Format Painter icon is the source cell and any cells you click will inherit that cell’s formatting. 25. Now, click on cells A13-A18 to format them with the indentation. Repeat on these groups of cells: A20-A27, A29-A30, A32-A36, A38-A42, A44-A50, and A52-A57. 26. Click on the Format Painter icon to turn format painting off. 27. Now, all of the cells in the spreadsheet should be formatted with the proper indentation to this point. There’s one more set of cells we want to indent – the category titles under the Expenses section. 28. Click on cell A12. Now, we want to select multiple cells which are not next to each other. Hold down the CTRL key on your keyboard and click on cells A19, A28, A31, A37, A43, A51.

29. You will now have multiple cells selected. 30. Click on the

button in the Alignment section of the Home ribbon.

31. 32. Change the Indent section to 1 by clicking on the up arrow. 33. Click OK. 34. All of those labels are now indented by one space. 35. Save the spreadsheet. You have performed a lot of great formatting on the spreadsheet. In creating this budget, we made a mistake in the “Income” section. We used “Wages” as a category header when we really should have used “Income” and had “Wages” as a separate item.

Editing a Cell and Inserting a New Row

1. Click in cell A2. 2. Start typing Income. Excel “guesses” what you are typing based on other information in the column:

3. Since we already have something in the column which starts with Income Excel utilizes type ahead as a shortcut. If you wanted this cell to read Income Total, you could press Enter, but we don’t, we want just the word Income. 4. Finish typing the word Income and when you come to the end, press the Delete key.:

5. Now cell A2 simply says Income, press the Enter key. 6. We want a new row in-between Income and Interest/Dividends. 7. Right-click on the 3 in row 3 and select Insert:

8. 9. Now, a new row 3 is inserted:

10. Click on cell A3 and type in Wages and press Enter. 11. Wages took on the formatting of cell A2. When you insert a cell, it takes on the formatting of the cell above where it is being inserted. We want it to look like cell A4. 12. Click on cell A4. Single click on Format Painter in the Clipboard section of the Home ribbon. 13. Click on cell A3. 14. Now, cell A2 (Wages) should be in the same format as the other categories:

15. Now, let’s widen the B column so we can enter data into it. 16. Right-click on the B column label and select Column Width.

17. Change the column width to 14 and click OK: Congratulations! You have created a really nice looking budget so far. Here’s a copy of the file in case you want to start from here: Family Budget 2.xlsx (zipped) Now that we have a framework for the budget, let’s starting entering some data and filling it out. Start at the top and fill out the sections as illustrated below: Want to skip this data entry? Family Budget 3.xlsx (zipped)

Don’t add the income line items up, just leave Income Total empty for now. Later, we will create a formula to add those cells up. It’s time to move on to the next section:

And the next section…

Good job so far! Now, let’s move to enter some more expenses:

And finally, the rest of the expenses (we spend way too much money!):

We’re finished entering data, so let’s format the numbers. Click on cell B3 and hold the left mouse button down, now drag down to cell B58:

Click on the in the Number section of the Home ribbon. This will format the entire column in dollars:

We have rounded all of our dollar figures, so we want to eliminate the .00 from each of the cells. Click on the icon in the Number section of the Home ribbon twice. Now, your formatting should look like this:

Congratulations! You have entered your data and formatted it into dollars. In the next section, we will continue formatting this document

Formatting Your Excel Spreadsheet In this section of the Microsoft Excel 2007 tutorial, we’re going to continue working with our Family Budget spreadsheet and start really formatting some cells to make the budget stand out. To recap on where we are with the Family Budget: we have designed the framework for the budget, we have started formatting some of the cells, and we’ve entered some numbers in the budget. Here’s a copy of the budget up to this point, in case you have skipped ahead, or if you didn’t want to do all that manual work in the beginning of the tutorial! Family Budget 4.xlsx (zipped)

Formatting Cells

We need to take this plain vanilla, boring budget and turn it into something easy to read and spectacular! 1. Let’s start by centering the word Budget in cell B1. Click on B1 and click the the Alignment section of the Home ribbon.

icon in

2. Let’s bold print the cell by clicking the

icon in the Font section.

3. Select row 2. 4. Click on the pull-down arrow on the Fill Color icon Home ribbon.

in the Font section of the

5. Change the color to Olive Green, Accent 3, Lighter 60% (the second light green down). Your row should look this:

6. Now, let’s repeat this process for row 8 (Investments) and row 12 (Expenses). 7. Click on 8 on row 8. Click on the pull-down arrow on the Fill Color icon in the Font section of the Home ribbon. Change the color to Olive Green, Accent 3, Lighter 60% (the second light green down). 8. Click on 12 on row 12. Click on the pull-down arrow on the Fill Color icon in the Font section of the Home ribbon. Change the color to Olive Green, Accent 3, Lighter 60% (the second light green down

Using Styles to Set Formatting Microsoft Excel 2007 can use formatting to style cells, rows, and columns. This ensures if you decide you want to change formatting later in a spreadsheet, you can simply change one style and have multiple cells update automatically. We are going to format the category headings under Expenses, but we’re going to use styles in case we want to change the formatting later. 9. Select row 13.

10. Click on the Cell Styles button in the Styles section of the Home ribbon. 11. Click on New Cell Style.

12. Enter the Style Name: of Expense Cat. 13. Check the Font checkbox. 14. Click the Format button. 15. Click on the Fill tab.

16. Select the fourth green down – the one which is highlighted in the picture above. 17. Click OK. 18. Click OK. 19. Click on row 13. 20. Click the Cell Styles button. 21. At the top, under Custom, select Expense Cat.

22. Unfortunately, the black font doesn’t look that good – so let’s change it!

23. Click on Cell Styles. 24. Right-click on Expense Cat and select Modify. 25. Click the Format button. 26. Click on the Font tab.

27. Under the Color section, select White. 28. Click OK. 29. Click OK.

30. Ok, that looks better. Now let’s apply the style to the other category rows. 31. Select rows 20, 29, 32, 38, 44, and 52. You select them by clicking first on row 20, then hold down the CTRL key and click on each of the other rows. 32. Now, click on Cell Styles and click on Expense Cat. 33. Save the spreadsheet. Great job! Here’s a copy of the spreadsheet up-to-date:

Adding Additional Columns We need to enter columns for the months of the year so we can compare our budgeted numbers with what we actually spend each month. First, let’s put in the months. We’re going to put them in every two columns so we have room to enter our actual and then to calculate whether we are over or under budget. 1. Select row 1. 2. Right-click and select Insert. This will insert a new row at the beginning of the spreadsheet. 3. Click on cell C1. 4. Type in January. 5. Now click on cell E1 and type in February. 6. Click on cell G1 and type in March. 7. Repeat this process for each month, skipping one column in between the months, until you have entered December in cell Y1:

8. Go back to the top left of the spreadsheet and click in cell C2. 9. Type in Actual and press the Tab key. 10. You should now be in cell D2. Type in Difference in cell D2 and press the Tab key. 11. In cell E2, type in Actual, press Tab, then type in Difference in cell F2. Repeat this process for each month:

12. Don’t worry right now about the size of the columns, we’ll resize all those Difference columns later to make the label fit. 13. Go back to the beginning of the spreadsheet to cell C1.

Centering Column Labels Across Multiple Cells

14. Now we’re going to center the January label over both Actual and Difference. 15. Click in cell C1, hold your mouse button down, and move to the right one cell so both C1 and D1 are selected: 16. Click on the

button in the Alignment section of the Home ribbon.

17. 18. The January label is now centered across columns C and D. If you wanted to unmerge and center it, simply click the button again. 19. Now, we want to repeat this formatting across each of the months. We’re going to use Format Painter. Click on January, then click on Format Painter twice. 20. Now, select February by clicking in E1, holding the mouse button down, then drag into F1. 21. Repeat with G1 and H1, and so on until all the months are merged and centered over their respective “Actual” and “Difference” columns: 22. When they are all complete, click once on Format Painter to disable it.

23. Rows 1 and 2 seem important, so let’s bold print them. 24. Select rows 1 and 2 by clicking on 1, holding the mouse button and dragging down one row. Click on the Bold button or press Ctrl+B on the keyboard. 25. We’re missing some “total” lines in each of the sections, so let’s add those now. 26. Right-click on row 13 and click Insert. 27. This will insert a new row 13. Click on cell A13. Type in Investments Total.

28. Cell is indented like the cells above, so we need to reset that formatting. 29. Click on cell A8, press CTRL+B to make the cell Bold. 30. Single click on the Format Painter button. Click on cell A13.

31. Now the cells should be formatted correctly. 32. Right-click on row 22, click Insert. 33. Click on cell A22. 34. Type in Housing Total. 35. Press Enter. 36. Press ALT+H then ALT+F ALT+A. This will open the Alignment dialog box.

37. Change the Indent to 1. Click OK. 38. Now, repeat this process to add “total” lines to each of the sections in the Expenses section of the spreadsheet. 39. When you get to the bottom, you should be at row 67 for Debt Repayments Total:

40. In row 68, type in Expenses Total and change the Indent to 0. 41. Select row 68 and make it Bold. 42. Go back through and make the “total” rows (row 22, 32, 36, 43, 50, 59, and 67) Bold.

43. Save the spreadsheet. Congratulations! You have done a great job formatting the document. In the next section, we’re going to explore formulas and start having the spreadsheet perform some calculations for us.

Working with Formulas In the previous sections, you have created a pretty budget using a lot of great formatting techniques. In this section, we’re going to start having Excel 2007 do some heavy lifting for us and starting having it perform calculations. If you’ve skipped ahead or just want to start with a fresh copy of the spreadsheet with where we are at in the tutorial, download it here: Family Budget 6.xlsx (zipped)

What is a Formula? An Excel formula is an equation containing functions, cell references, numbers, and/or operators. Excel is very powerful and can perform complex calculations which are updated as easily as entering data in a cell. It has simple functions – such as summing several cells – and much more complex statistical functions. We’ll focus in the section on the simpler formulas to calculate information we need in our budget.

Creating a Formula 1. Open the Family Budget spreadsheet. 2. Click in cell B8. 3. Click on the Enter:

button in the Editing section of the Home ribbon and press

4. The AutoSum button sums the cells which are above and closest to the cell you are in. It stops summing at the first break – in the case, cell B3 has no data, so it is the first break. 5. Click on cell B13. Type in =sum(B10:B12) and press Enter. 6. B10:B12 is a range of cells – it tells Excel to sum all of the cells in the range: B10, B11, and B12. 7. Click on cell B22. Type in =sum(

8. Click in cell B16, hold your mouse button down and drag down to cell B21. Press Enter. 9. Now you’ve learned three ways to sum cells – using AutoSum, typing in the cell references directly, and using the mouse. 10. Practice your favorite method by summing each of the “total” rows in the Expenses section. 11. When you get down to cell B68, we will need to use a different method to sum the cells. Since the Expenses section contains a total row in each section, you don’t want to simply sum the entire section or you will be double counting.

12. Click on cell B68. Type in =sum( 13. Go to the top of the Expenses section and click once on cell B22. In the formula bar at the top, you will see it add cell B22 to the formula: 14. Now hold down the CTRL key and click on cell B32: 15. Continue the process of holding down the CTRL key and clicking on cells B36, B43, B50, B59, B67:

16. Press Enter. 17. Rows 8 and 13 aren’t bold across all of the cells. Select row 8 and press CTRL+B twice. Repeat with row 13. Why do we have to press it twice? The first cell in the row is already bold, pressing it once unbolds the row, pressing it a second time bolds the entire row. Great job on entering those formulas! Here’s a copy of the spreadsheet with the formulas in it so you see it before we move on to finishing all of the formulas: Family Budget 7.xlsx (zipped) Now, we’re going to enter some data for the month of January so when we create the formulas for each month, it will calculate and we can quickly see if we did it right. Here’s the data we want to enter:

Now, we need to add some more formulas, so let’s get right back into that: 18. Go to cell D4. 19. Type in =C4-B4 20. Press Enter. 21. This formula subtracts cell B4 from cell C4, providing the difference in cell D4. 22. We could go down the spreadsheet and type the formula in each cell but there is a much easier way! 23. Click on cell D4.

24. In the bottom right of the box on cell D4 is a little square box. This is the fill control. There are two ways to use it – click on the small square and drag up, down, right, or left and it will fill those cells with the contents of cell D4.

25. The other way to fill the contents of the cells down is to double-click on the small square in the bottom right of the cell.

26. This will fill to the first break – the adjacent column C stops at C8, so the D column will automatically fill down to D7, stopping at D8. 27. Earlier, the formula we entered in D4 was =C4-B4, but as you can see on the spreadsheet, each cell is properly calculated the cells to its left – they don’t all have the formula =C4-B4. Excel automatically updates formulas based on where they are copied to – reflecting the intent of the original formula based on its new position. So, for example, it updated the row number for D5 to 5 (so the formula is now =C5-B5), for D6 to 6 and so on. 28. The same update occurs if you copy the formula to a new cell. Click con cell D6 and press CTRL+C to copy the contents of the cell. 29. Click on cell D10 and press Enter. 30. You will notice the formula in cell D10 is updated to reflect its new location. 31. Double-click on the square on the bottom right of the box in cell D10 to fill the contents of D10 down. 32. Now, copy D12 into D16. Fill down the contents of the cell. Repeat this process through all of the sections. 33. Return to the top of the spreadsheet and click on cell B8. 34. We want to fill the formula in this cell across all of the months. Click on the small square in the bottom right-hand side of the cell and hold the mouse button down as you drag it right to cell Z8:

35. Now repeat this process with each of the “total” rows. 36. The C column doesn’t have the proper $ formatting like the other cells. Let’s fix that – click in cell C4. 37. Click the Format Painter button twice. 38. Click and drag down for cells C4:C7. Now repeat in each section which doesn’t have the correct formatting. You do not want to drag all the way down the column or you will unbold the “total” rows. 39. Click the Format Painter button once when you are finished to turn it off. 40. Here’s what the spreadsheet looks like so far:

41. Go to cell F4. 42. Type in =E4-B4 43. Press Enter.

44. You may notice a problem here. Though the spreadsheet is accurate – we haven’t earned $4350 yet, so we are negative on wages – but when we use this formula all the way down, the spreadsheet will be a little cluttered – just because the month hasn’t o Using the “IF” Statement in Excel 2007 45. Excel has a very handy formula to make the value which appears conditional. 46. Click on cell F4 and press the Delete key. 47. Type in =IF(

48. Excel explains the formula, If(test formula,is true than do something, is false then do something else). 49. Finish typing the command =IF(E4=””,””,E4-B4) 50. Now, you’re not going to see a value in the cell:

51. E4 is empty. Type a value of 4200 in cell E4.

52. Now, once you have a value in E4, the contents of F4 changes. 53. Delete the contents of E4. 54. Let’s go over to cell D4 and change the formula to match our new conditional formula. Type in =IF(C4=””,””,C4-B4) 55. The contents of D4 do not change because C4 is not empty. 56. Delete the contents of C4. Now D4 will be empty. 57. Fill the new formula in D4 down to the other cells in column D as we did earlier. Skip the “total” rows. 58. Now, click on cell F4. Fill the formula down to the empty cells in this column. Since there is no data in the Actual column, all of the cells will appear empty. 59. Fill in some random numbers in column E to see if the formulas are working correctly. When you are finished, delete the random data you entered. ccurred yet. What can we do about it? 60. Now let’s go to cell F4 again. 61. Edit the formula and change it to =IF(E4="","",E4-$B4) and press Enter. 62. The only thing you are change is placing a $ sign in front of the reference to cell B4. This change is to place an absolute reference to column B. When you copy or fill this cell, the reference will always stay with column B, but change the rest of the formula. 63. Now click on cell F4 and press CTRL+C to copy the formula. Paste it in cell H4. View the formula in cell H4: 64. The “B” part stayed the same, while the rest of the formula changed to reflect its new location. 65. Now, fill the formula down in the column to all of the empty cells. 66. Now repeat the process of copying the formula over to the rest of the “Difference” columns for the other months until all of them have the formula filled in.

67. Remember to go back to January and all of February and change the formula to match th Additional Cell Formatting one location.

68. Now, let’s get the column width correct. Select columns C through Z. 69. Right-click on column Z and select Column Width.

70. Type in 12 and click OK. 71. All of the columns should now be a width of 12. 72. We need to make it obvious to the end user which cells they should fill in, so let’s color them. 73. Select cells B4:C7

74. 75. Select the cell fill color icon in the Font section of the Home ribbon. Click on the pull-down arrow and select More Colors….

76. Select the third yellow down from the center and click OK.

77. Now we’re going to repeat the coloring in all of the sections where a user will edit or enter data – so the Budget column, and each of the Actual columns:

78. Now, we’re going to add some borders to the document. Select cells A4:Z8. 79.

80. Click on the Borders pull down in the Font section of the Home ribbon. Click on All Borders. 81. Now repeat in each section – skipping the colored title rows.

82. We need a Total row at the bottom to add everything up. 83. Click on cell A69. 84. Type in Total and press Enter. 85. Click on cell A69. Change the Indent to 0. 86. Click on row 69. Make it Bold. 87. Select A69:Z69 and set All Borders. 88. Click on the Borders pull down menu and select Top and Double Bottom Border. 89. Click on cell B69. 90. Type in =B8-B13-B68 which calculates the Income Total – Investments – Expenses . 91. Fill the cell to the right across all of the cells to Z69. 92. Select row 2 and click on the Center icon on the Alignment section of the ribbon. 93. Click on cell A1. 94. Select cells A1:Z69 (the entire spreadsheet).

95. Pull down the Borders button menu and select Thick Box Border. 96. Save the spreadsheet. Congratulations! You have formatted a great spreadsheet!

. Changing Formatting Based on Value, aka Conditional Formatting One of the features which makes Excel great to use is Conditional Formatting. Conditional Formatting is exactly what it sounds like – changing the formatting of a cell based on a value condition which you specify. For example, you could set a condition that any values above 100 cause the contents of a cell to turn red, while values above 1000 turn the cell green. There are endless possibilities and Excel 2007 makes it much easier to use Conditional Formatting than previous versions. 1. Click on cell D4.

2. Click Conditional Formatting in the Styles section of the Home ribbon. 3. Move your mouse over Highlight Cells Rules. 4. Click on Greater Than.

5. 6. Change the value to 0. 7. Click on the pull down menu to the right and select Custom Format.

8.

9. Select the dark green color at the bottom of the Color pull down menu on the Font tab. Click OK. 10. Click OK. 11. Now change cell C4 to $4500. Cell D4 changes to Green.

12. Now repeat the process, but this time, select Less Than, change the value to 0 again, and select a format of Red text. 13. Change cell C4 to $4200. Now, the $(150) is red. 14. Using Format Painter, paint the format of cell D4 down column D, skipping the Total cells.

15. We want the format on all of the months – plus we need to paint the $ format on the Actual column to the Actual columns. Select columns C:D. 16. Click on Format Painter twice. 17. Click on columns E:F. Repeat through each set of columns for each month on the spreadsheet. 18. Click Format Painter once to turn it off. 19. Save the spreadsheet.

Printing Your Document Formatting Your Document for Print Good work so far on the Family Budget spreadsheet. Here’s a copy of the spreadsheet so far if you are skipping ahead or want a reference point to where we are at: Family Budget 9.xlsx (zipped) Now it’s time to format the document for printing. First, let’s look at how the document looks before formatting.

Print Preview 1. Click on the Office button. 2. Hover over Print and select Print Preview.

3. Currently, the budget will print across 8 pages. Let’s change some formatting to see if we can cut that down. 4. Click on the Page Setup button and select Landscape. Select Fit to 1 page wide by 1 tall.

5. Click OK.

6. Now the document has become really difficult to read! 7. Let’s see if we can find a combination which makes more sense. Click on Page Setup.

8. Change the Fit to option to 2 pages wide by 1 tall. Click OK.

9. One of the downsides right now is the second page does not have the categories on the left. We can fix that through Page Setup.

10. Click Close Print Preview. 11. Click on the Page Layout ribbon.

12. Click on the Page Setup button – the button in the bottom right of the Page Setup section of the ribbon. 13. Click on the Sheet tab.

14. In the Columns to repeat at left: field, enter A:B 15. Click OK. 16. Click on the Office button. 17. Hover over Print and select Print Preview. 18. Arrow down at the right to go to the second page.

19. Now, columns A and B are repeated on the second page.

Congratulations! You have completed the first Excel 2007 course. We’ve learned the new interface in 2007 and how to create an excellent family budget document using a lot of the great features in Microsoft Excel 2007. Whether you are a home user looking to use Excel for its powerful spreadsheet functions or a business professional looking to analyze trends, Microsoft Excel 2007 has features for you. By taking a free Microsoft Excel tutorial like this one, you are well on your way to becoming an Excel expert. Stay tuned for future free Microsoft Excel 2007 tutorials.