# Spreadsheets

**Part 1: Basics**

You are living on your own space. Your own car. Your own BILLS!! Use Microsoft Excel to create your own budget as in Figure 1 below.

Figure 1

- Open MS Excel
- Create a blank workbook
- Save your Excel workbook as “Homework 3”.
- Create a worksheet exactly like in figure 1. Change the title of the sheet to “your name’s” Expenses.
- Use Auto fit, Auto fill, and Auto complete features in Excel to create the sheet faster.
- Rename sheet 1 to “Expenses”.
- Add all borders to the range A2:M8. (Border options are located in the “Home” tab-font section)
- Save, DO NOT close the workbook, and move on to part 2

** **

** **

**Part 2: Formulas**

- Add a new Worksheet to your Homework 3 Workbook
- Rename the new Worksheet to “Math operations”
- Merge and Center cells A1:E1 and type “Working with Excel Math” in there
- Apply Title style to cell A1(Home tabàStylesàcell stylesàTitle)
- Type “Addition” in cell A2
- Type “Subtraction” in cell A3
- Type “Multiplication” in cell A4
- Type “Division” in cell A5
- Auto-fit the contents in column A
- Insert a row above row #2
- Type “Basic Math” in cell A2
- Merge and Center the text in cell A2 to the range A2:E2
- Apply Heading 4 to cell A2(Home tabàStylesàcell stylesàHeading 4)
- Apply the Calculation Style to the range A3:A6(Home tabàStylesàcell stylesàCalculation)
- Type = 4 + 6 / 2 in cell B3 then type = (4 + 6) / 2 in cell D3
- Type = 6 – 6 * 2 in cell B4 then type = (6 – 6) * 2 in cell D4
- Type = 2 * 2 + 6 in cell B5 then type = (2 * 2) + 6 in cell D5
- Type = 9 / 3 + 4 in cell B6 then type = ( 9 / 3 ) + 4 in cell D6
- Add all borders to the range A1:E6
- Here is how your sheet would look like.

- press Ctrl + ~ and this what would see
- Save, DO NOT close the workbook, and move on to part 3

** **

**Part 3: Import and analyze data **

- Download the Expenses.txt file; it’s attached to Homework 3 on blackboard.
- Back to Excel workbook “Homework 3”
- Add a new worksheet to “Homework 3” workbook.
- Name the added worksheet “My Budget”.
- Import the data from Expenses.txt into “My Budget”
- Insert a table in the Range A2:M11
- Merge and center the cells A1:P1
- Format cell A1: font color=red, font size=18, Fill color=yellow and make the text bold.
- Rename sheet 1 to “Expenses”.
- Insert the word “Total” in cells N2 and A12. In column N, insert
**formulas**to find the total of each row. In row 12, insert**formulas**to find the total of each column, including column N. Once you have completed this sheet, cell N12 should show the grand total of all expenses for the year. - Insert the text “Monthly min” in cell A14. In row 14, insert
**formulas**in cells B14:M14 to find the minimum expense of each month. - Insert the text “Monthly Max” in cell A15. In row 15, insert
**formulas**in cells B15:M15 to find the Maximum expense of each month. - Insert the text “Occurrences” in cell A16. In cell B16, insert
**formulas**to count the occurrences of the number 0 in range B3:M11(Use the countif function) - Insert the text “sum if value>800” in cell A17. In cell B17, insert
**formulas**to find the total of all the cells in the range B3:M11 that contain values greater than or equal $800 (Use the sumif function) - In O2 add the text “Expense Ratio”
- In cells O3:O11 insert
**formulas**to insert “High Expense” if the total in column N is more than 400 and “Okay Expense” if the total is not greater than 400.(use if function) - In P2 add the text “Expense average”
- In cells P3:P11 insert
**formulas**to find the average of each expense.

- Add all borders to the range A1: P15. (Border options are located in the “Home” tab-font section)
- Create a 3-D Column chart that shows monthly expenses for rent, utilities, food, travel, tuition, and car insurance for the month of June.
- Type “Monthly Expenses for June” in the chart title.
- Move the chart to a new worksheet titled “June”.
- Save and submit your workbook under Homework3 on the blackboard.