Microsoft Office/Create a Personal Budget with functions

Contents

Function - pre-written formula built into Excel that takes different values and performs an operation: SUM, AVERAGE, MAX, MIN

Order Of Operations - PMDAS (Please Excuse My Dear Aunt Sally)

P - parentheses

E - exponents

M & D - Multiplication and Division taken left to right

A & S - Addition and Subtraction taken left to right

Point Mode - selecting cells for a formula by clicking on them with the mouse

Smart Tags - Dates, financial symbols, people's names, indicated by a small purple triangle in the cell. Clicking on this purple triangle gives a list of actions you can perform for that cell

Smart Tag Indicator - the small purple triangle itself is the indicator that a smart tag is available

Arguments - The values that you plug into a function are called the arguments. In a SUM function it would be the list of cells you want to add together: =SUM(C2:C15) This means to add the values in the cells from C2 down to C15.

Average - Sums the numbers in a range and then divides by the number of cells in that range.

Max - A function that will display the highest value in a range of cells

Min - A function that will display the lowest value in a range of cells

Range Finder - A helping function in Excel that checks which cells are referenced in a formula. Very convenient if you have accidentally clicked on the wrong row or column for one of the values.

Accounting Number Format - displays the numbers as a dollar value with the dollar sign to the left of the numbers, a comma every three digits and rounded to the nearest hundredth.

fixed dollar sign - the dollar sign in a cell is to the far left

floating dollar sign - the dollar sign is just to the left of the numbers

Comma Style Format - uses commas but no dollar signs in the numbers

Conditional Formatting - Formatting that changes depending on the value in the cell, or what condition the value meets. You could change the color of numbers to blue for any number over 1,000,000 for example.

Condition - A relation between at least two values. If a number is less than 7 then ..., this is a condition.

Pixel - The smallest element of a graphic. It represents on dot of color on a screen. The size of the dot is based on your screen resolution. A resolution of 1024X768 means that you have 1024 pixels across and 768 pixels down the screen.

Spell Checker - An operation in Excel that looks for possible spelling errors in a worksheet, comparing the words against a standard dictionary of words.

Page Layout - a view of the worksheet that lets you edit the worksheet while seeing how it will look in printed form.

Normal View - the default view of Excel allowing you to adjust the margins, header or footer.

Previewing the Worksheet - When you print an Excel Worksheet you should ALWAYS print preview before printing. This allows you to check to see what is being cut off and placed on a separate page. It also lets you see if you have a stray character that will cause you to print 100 pages instead of just 1.

Values Version - this is the default view and lets you see the results of the formulas and functions in each cell.

Formulas Version - this lets you see the functions and formulas in each cell instead of the value from the computation.

Debugging - the process of checking your formulas or functions to find errors.

In-cell editing - Double clicking on the cell and making the changes in the cell itself.

Best fit - Double clicking between two rows or columns and letting Excel choose the best width or height depending on the largest contents of a cell in that row or column.

Once you have your formulas into excel you might need to go back and verify that you have entered them correctly. If you single click on a cell that has a formula then you will see the formula in the formula bar and the value from the formula in the cell.

If you double click on the cell with a formula the formula will show up in the cell and the RANGE FINDER feature will be turned on. This will outline the different cells that are used in the formula to make it easier to verify that you have entered the formula correctly.

This is very handy in the case where you accidentally click on a cell in a row lower than you meant to.

So far the spreadsheet is considered to be mostly unformatted. An easy way to format a worksheet is to apply a THEME. You can change the theme of a worksheet from the Page Layout tab. In the far left there is a themes button. Play with the themes for the worksheet until you find one that you like.

Format the headings using the Cell Styles button from the HOME tab. The options here are dependent on the THEME you chose earlier. You should format each heading (row A and B) separately to give the main heading more emphasis than the sub heading.

Change the background color by selecting the cells you want to change and then clicking on the Fill color button from the HOME tab (it looks like a paint bucket).

Add a border around the headings from the BORDERS button in the HOME tab. Chose both headings at one time, then click on the Thick Box Border.

Accounting Number Format - When working with number such as stocks it is good to use Accounting Number Format. This will place the dollar sign to the left of the number (as a fixed dollar sign). It will also insert commas every third position to the left of the decimal, and put the decimals to two places (hundredths). A negative number will be displayed in parenthesis.

Comma Style Format - This will place a comma every third position to the left of the decimal.

Percents - Will move the decimal two places and place a percent sign at the end.

If you want to change the number of decimals then click on the increase or decrease decimal buttons in the NUMBER group.

Conditional formatting lets you change how a cell is formatted based on the values in the cell. In this case we will use the example of changing negative percent gain/loss to red so we can see them more easily.

Click and drag over the range J4:J12

From the HOME tab click on CONDITIONAL FORMATTING

Click on NEW RULE

Click on FORMAT ONLY CELLS THAT CONTAIN

In the edit rule section at the bottom set FORMAT ONLY CELLS WITH: to Cell Value

change the next drop down to LESS THAN

type in the value 0 in the last field.

Click on the FORMAT button

Go to the FILL tab

Click on a color for a background color

Go to the FONT tab

Change the color of the font so it will show up with your background color

The current view you are in is NORMAL VIEW. Change to page layout view by clicking the page layout button in the lower right of the window. Notice what is going to be cut off when you print and placed on a separate page.

I usually do my page layout from the print preview screen.

Go to the Office Button

Click on PRINT

Click on PRINT PREVIEW

Click on Page Setup

Click on Landscape because this table is wider than it is tall

Click on Fit to -1- page wide by -1- page tall

Click on OK

I then usually print from the Print Preview screen because it is a WYSIWYG (What you see is what you get).

NOTE - If you go to the SHEET tab from the Page Set up dialog box you can turn on grid lines to make your spreadsheet easier to read.

Create a personal budget for the next year. Pick one of the occupations that you have been looking at, or if you have a job currently use that.

Include rows for:

Income

auto gas

groceries

Eating out

going to games

going to movies

movie rental

music downloading

other expenses

totals

Include columns for each month in the year. Fill in the data as best you can, know that you do more of certain things in the winter versus the summer and certain things in the summer more than the winter.

Add columns at the right for Total, Average, Highest, Lowest

For the Total at the bottom be aware that income is money coming in and all the rest is money going out - hint hint hint

Format the spreadsheet

Create a Pie Chart to show the expenses you incur FOR THE WHOLE YEAR only.