Microsoft Excel Personal Budget How To

Microsoft Excel Personal Budget How To Microsoft Excel is a spreadsheet program. Use Microsoft Excel with students to organize data, perform calculations, and create charts (graphs). Spreadsheets or charts created in Microsoft Excel can be imported into Microsoft Word or PowerPoint documents. Microsoft Excel also can be used to create databases that allow you to organize, sort, and manipulate data. In this How-to a Microsoft Excel spreadsheet will be created so students can calculate and analyze their personal budget. This activity supports the Career and Technology Education, Business Education - Financial Management. Students will analyze factors that influence decision making and goal setting in order to develop an individual financial plan. Get Started A Microsoft Excel file is called a Workbook. A Workbook may contain one or many Worksheets. Typically, a new workbook will open with three worksheets.  Open Microsoft Excel. A Workbook will appear with Sheet 1 active. The rectangles on the worksheet are the Cells that are used to contain data. This data can be text, numerical data, or formulas. The Cell is identified by its column letter and row number. For example, Cell A1 is in column A and row 1 and is in the upper left corner of the spreadsheet.  To select a Cell to enter data, place the cursor on the Cell and click the mouse button. Cell A1 is the active Cell in the example to the right. Entering Text Data in Cells  Click on Cell A1 to make it active. Type “Budget Category” and press the Tab key. This will enter the text data and move the cursor to Cell B1. Notice that the text appears in the Cell as well as in the Formula Bar. See the example on the right. In Cell B1, type “Cost Per Month.” Press Tab. In Cell C1, type “Cost Per Year.” Press Tab.   Page 1 of 5 Baltimore County Public Schools Office of Instructional Technology: Microsoft Excel 2003 Personal Budget How To Revised June 2004 Resizing the Columns  Place the cursor on the line between letters A and B in the Column Headers bar. When placed exactly, the cursor changes to a double-headed arrow. Double-click and the column will resize automatically. Microsoft Excel will determine the width based on the number of characters in the Cell. To determine the width independently, place the cursor on the line between letters A and B in the Column Headers bar, when the curser appears as a double-headed arrow click once and drag the line to the right, expanding the size of column A until the heading fits inside the Cell. Resize the other columns until satisfied with the appearance. After entering the numerical data, resizing of the columns may again be necessary. Entering Data: Text and Numbers  Click on Cell A2. Type “Housing” and press the Enter key. This will enter the data and move the cursor down the spreadsheet to Cell A3. (Notice that pressing Tab will generally move one Cell to the right, while pressing the Enter key will generally go to the next row down, directly underneath the last place that was clicked.) Continue in this manner moving down the spreadsheet entering the following data: Transportation, Groceries, Childcare, Healthcare, Entertainment, Eating Out, Clothing, Utilities, and Telecommunications. Telecommunications should be in Cell A11. In Cells B2 through B11 under Cost Per Month, enter your estimated budget for each category. The spreadsheet should now resemble the example to the right. Resizing of the columns may be necessary to view the numbers. Text, by default, aligns to the left of a Cell and numbers align to the right of a Cell.       Entering a Formula  Microsoft Excel uses the equal sign to recognize when a formula (versus plain data) is entered in a Cell. Click on Cell C2 and type “B2*12” and press Enter. The cost per year will appear in Cell C2. When you click again on Cell C2, the formula used in that Cell appears in the Formula Toolbar. Page 2 of 5 Baltimore County Public Schools Office of Instructional Technology: Microsoft Excel 2003 Personal Budget How To Revised June 2004  The formula can be edited if needed in the Formula Toolbar. Copying the Contents of a Cell    To copy the formula in Cell C2 to Cells C3 through C11, first click on Cell C2 to select it. Move the cursor to the fill handle in the lower right corner of the Cell. The shape of the cursor changes from an expanded plus sign to a narrow one. Click and drag down to Cell C11. When the mouse button is released, numbers will appear. The formulas are relational, i.e., the formula in Cell C2 will use the data in Cell B2; the formula in Cell C3 will use the data in Cell B3. Click on any other Cell to deselect the group.  Note: If teachers only need to budget for items during the school year, they can multiple the Cost Per Month by ten. Formatting Cells Containing Text   Click and drag from Cell A1 to C1 to select the text for formatting. Use the Format menu and select Cells. The Format Cells window appears. Across the top of the window are six tabs: Number, Alignment, Font, Border, Patterns, and Protections. Clicking a tab opens it in the window. The Number tab allows the selection of the Category. Click the Alignment tab and change Horizontal Text Alignment to Center. Next, click the Font tab. The frame to the right will appear. Select Arial font, Bold font style, and size 10. Click OK.    Formatting Cells Containing Numbers   To format the numbers in column B and C, click and drag from Cell B2 to C11. Go to the Format menu and select Cells. Page 3 of 5 Baltimore County Public Schools Office of Instructional Technology: Microsoft Excel 2003 Personal Budget How To Revised June 2004   At the Format Cells window, select Number. Select Currency and click OK. Using the Chart Wizard – Step 1 The ease with which data can be displayed in a variety of charts is a powerful feature of Microsoft Excel. A number of charts can be tried before deciding on the ones that are most appropriate for the data. This task shows the basic use of the Chart Wizard as well as the necessity for carefully selecting the data for creating the chart.  Click and drag from Cell A1 to C11. This selects the entire range of data.  Go to the Insert menu and select Chart. This begins Step One of four steps in the Chart Wizard.  Use the Pie chart type and the Pie sub-type (this is the default). Use the Press and Hold to View Sample button beneath the subtypes to see a preview of the data with the selected Chart type. The preview indicates that all the data will not be represented in the chart. This is because of the extreme range of data in the three columns.  Click the Next > button. Using the Chart Wizard – Step 2     In the Chart Source Data window, click the Series tab. The Series box on the left side contains the data sources. Select Cost Per Month and click the Remove button. Now, this chart will only show the cost per year data. In similar manner, charts can be created displaying only the cost per month. Click the Next> button. Using the Chart Wizard – Step 3  Step three of the Chart Wizard is used to make changes to the format of the chart such as the title and the location of the legend. A series of tabs run across the top of the window. As changes are made, they are reflected in the preview panel. Page 4 of 5 Baltimore County Public Schools Office of Instructional Technology: Microsoft Excel 2003 Personal Budget How To Revised June 2004   Select the Titles tab and change the Chart title to “Personal Budget 2004.” Click the Next> button. Using the Chart Wizard – Step 4  Step four provides a choice of placing the chart as a new sheet or as an object in an existing worksheet. Select As Object in… and make sure the name is of the current spreadsheet in which you are working. Click the Finish button to see the result.  Page 5 of 5 Baltimore County Public Schools Office of Instructional Technology: Microsoft Excel 2003 Personal Budget How To Revised June 2004