Area Research and Tutorials
BASIC EXCEL/BUDGET
TUTORIAL COURSE
ARTs
ARTs
Jumpstart Education …..
We get you where you need
Area Research and Tutorials
This document should be downloaded and used during the class session. The
instructor lecture should take about 60 to 70 minutes. The course material is
designed to introduce to you the basic must know information and to be used over
and over for references. This document can be used without taking the class but
that will depend on factors such as previous subject matter knowledge. This
course will give you the must know information which you will need to get started
using the essentials
The information contained in this documentation is provided for general purposes
only. Depending on your personal computer and operating system, your screens
and procedures may vary.
For best learning results, please print this document and use it when listening.
Go to: www.artutorials.org, select courses to download course documents.
Basic Budget w/ Excel Table of Content
Area Research and Tutorials ... i
Basic Budget w/ Excel Table of Content ... 1
Excel Budget Basic Objectives ... 2
Excel Terms and Icons ... 3
Select the Starting Cell ... 4
Auto-Fill Copy ... 5
Entering Expense Titles ... 6
Entering Expense Costs ... 7
Copy and Paste Technique ... 8
Functions ... 9
Equations ... 10
Relative Copying ... 11
Modifying/Updating Cells Entries ... 12
Inserting a New Row ... 13
Copying Columns ... 14
Change the Numbers to Currency Format ... 15
Adding Additional Sections ... 16
Graphing Data ... 17
Basic Microsoft Excel Exercise ... 18
Basic Microsoft Excel Exercise Continue …... 19
Basic Microsoft Excel Exercise Continue …... 20
Excel Budget Basic Objectives
Select the Starting Cell
Auto-Fill Copy
Entering Expense Titles
Entering Expense Costs
Copy and Paste Technique
Functions
Equations
Relative Copying
Modifying/Updating Cells Entries
Inserting a New Row
Copying Columns
Change the Numbers to Currency Format
Adding Additional Sections
Excel Terms and Icons
Rows are the numbers down the left-hand side. Rows numbers range from 1 to 65,536.
Columns are the letters cross the top. Column letters range from A to IV, after column Z is column AA, after AZ is BA, and so on to IV.
A Cell is the intersection of a column and a row, example “A1” means column A and row1
Scissor is used to cut selected data from one location and paste to another area. The original data is removed.
Clipboard and paper is used to paste data, after cutting or copying data. This icon will be colored in when it is possible to paste data.
Two sheets icon is used for copying data from one location to another. The original data remains.
Moving around by using the Directional Arrow and/or the Enter Keys
There are always several ways to accomplished most tasks
We will use more of the icons from the tool bar and they will be
explained later.
Select the Starting Cell
To development our budget spreadsheet, let’s start in cell A5 (not so important). Enter the titles Income, and sub-titles Salary1 in cell B6 and Salary2 in cell B7. Type Totals in cell A8 as you see above.
When you are typing in a cell and afterward, the content is shown in the Formula bar fx. Typing a location here will move the cursor.
The current selected cell is called the active cell “A9” and is referenced left of the Formula
Bar.
The column and row containing the active cell are highlighted, making it easier to identify There are 256 columns and 65,536 rows, so a worksheet contains over 16 million cells. The number of cells you can use at any one time is limited by the amount of memory your computer has.
Auto-Fill Copy
Use the directional arrow keys to move to different cell locations. Enter 01/01/2010 and 02/01/2010 in cells C5 and D5 respectively. Notice the point located at the lower right corner of the cell D5. Drag this point across and to cell N5. The dates will be added continuously, but by days
After dragging, you will see a pop-up box with a + sign. Click the + sign or dropdown arrow, and a menu will appear. This menu has radio buttons with autofill options. Click on Fill Months to change the dates to continuous months.
Copy Cells
Fill Series
Fill Formatting Only
Fill Days
Fill Weekdays
Fill Months Fill Years
If you highlight C5 and D5, then drag D5 to N5 the dates will continue the series in Months
Entering Expense Titles
Add the Expenses titles and the Total row. Click on the cells (Totals) then clicking the right-side alignment icon. You can change both Total cells by using the Ctrl-key. We will explain how to insert more rows for additional expenses later.
Letters will always be left-side aligned and numbers will always be right-side aligned by default. However, cell alignments can be changed in using various techniques. Using the technique above is one way to align smaller sections of a worksheet.
Click on a column or columns (by highlighting multiply columns) and clicking any three alignments (Left, center, right) to align all of the selected cells
Click on a row or rows (by highlighting multiply rows) and clicking any three alignments (Left, center, right) to align all of the selected cells
Entering Expense Costs
Enter the estimated amounts for the salaries for only the first month. Also enter the costs for the expenses for only the first month, as you see above in the left sheet. These costs can be easily adjusted at anytime in the future. These figures are only used as a place-holder and should be replaced with the correct values when they are known. This will be explained.
To copy these first month figures to the other months, start by:
Select all the first month entries (by dragging the mouse from the first to the last cell) which identifies what to copy
Start the copy command by clicking the copy icon (double pages) on the task bar or by clicking the right mouse button and clicking copy. This tells Excel what you what to do with the selected cells.
Copy and Paste Technique
Continued …..
Tell the copy command where you want the data, by clicking in cell D6 and dragging the mouse to the last month N6 (N6 should be for 12/1/2010)
To place the selected cells, click the Paste icon on the task bar (clipboard & paper), or right click the mouse button and then click Paste.
Press the Esc key to terminate the copy command and the selected cells, because you can paste the selected data in more than one place.
After pasting the data from column C to the other columns, ranging from D to N, all the rows are relatively pasted in position. In other words, the placement of the selected cells are determined by the top row.
Functions
Click in cells C8 and C20 to select where you want the answer to the Sum function placed. Then click the Sum icon on the task bar and all of the connecting cells will be selected to be added. The selected cells will be displayed in the formula bar and have a blinking border. Press Enter to complete the task.
Performing these steps in cell C8 will select C6 and C7 to be added. Clicking in cell C20 will select C17 to C19 only. The sum selection will stop when an empty cell is encountered. Press Enter to accept these answers for now.
To have cells C11 to C19 added and have the sum place in C20, Click in cell C20 to display that function in the formula bar, then edit the formula listed to be “=SUM(C11..C19)” or you can just type this in cell C20.
Equations
In cell A22, enter Saving and in cell B22, enter 5000. This will represent your previous available funds
The equation we want is to add all the available incomes and subtract all the expenses from that total. In B22 has the available Saving and C8 has the total income for the first month which is B22+C8. C20 has the total expense so this must be subtracted from the total available income. Equations must start with an “=” sign. This means you are not typing in a letters but you want the contents of the cell, so =B22+C8-C20 will equate to what you will have left at the end of the first month.
Relative Copying
Use Auto-Fill to copy the Totals from C8 across the other months (D8 to N8), Totals from C20 across the other months (D20 to N20), then do the same Auto-Fill to copy the Saving totals from C22 across the other months (D22 to N22).
Did it work correctly? Let’s examine - get your calculator: C8 = C6+C7 => 5000 + 4000 = 9000
C20 = C11 to C19 => 2000 + 200 + 100 + 100 + 400 + 500 + 100 + 100 = 3500 C22 = B22 + C8 – C20 => 5000 + 9000 – 3500 = 10500
Now:
click in cells D8, E8, to N8 to verify the sums includes the correct cells ( via formula bar) click in cells D20, E20, to N80 to verify the sums are correct ( via formula bar)
click in cells C22, D22, E22, these figure are the total incomes minus the total expenses for a month, plus the saving from the previous month.
C22 = “=B22+C8-C20” D22 = “=C22+D8-D20” E22 = “=D22+E8-E20”
Modifying/Updating Cells Entries
Left Screen
Right Screen
Rule# 1: Always save your worksheet before making change changes. This allows you to close without saving your new changes and returns to where you started.
The Left Screen shows 500 added for insurance 3/1/2010, in C16. This increase the total expenses from 3500 to 4000, and the total saving is decrease from 21500 to 21000. The totals are always automatically re-calculated.
The Right Screen shows the misc changed to church in B18. This was done by over typing in the cell or in the formula bar. To have church expense in cell C18 equal to 10 percent of your total income for that month, enter “=C8*0.1” => 900. Copy this C18 to D18,, N18 using Auto-Fill. The totals will be calculated using the correct cell for that month income.
Therefore, we start this budget using some estimated costs, but as the actual costs are known, they should be changed by typing over what’s there.
Inserting a New Row
Left Screen Right Screen
To insert a row, click Insert on the Task Bar, and then click Rows on the menu. Rows are always inserted above the selected row or cell.
Left Screen:
The selected row was 20 (or any cell could have been selected on row 20), see the previous page, C20 became C21 and a new row 20 was inserted. The sum formula does not include the new row C20.
Right Screen:
The selected row was 19 (or any cell could have been selected on row 19), see the previous page, C19 became C20 and a new row 19 was inserted. The sum formula does include the new row C19.
Always insert a row within the sum range, to have the new row included. Hint: I always keep an extra misc expense just for this reason.
Copying Columns
To modify your budget worksheet to match your date in time, you will need to add and delete columns. Place the cursor in the column-row, then select the desired dates, by pressing and dragging. In this example, we included only 3 months, then click the copy icon. The selected columns will have a blinking border.
Our example has 1/1/2010 to 12/1/2010 in columns C to N respectively. So to continue a new year with 1/1/2011, we must copy any previous months after the last month (12/1/2010), so click on column O and click the Paste icon.
The dates will not be correct but the figures will, so perform an Auto-Fill Copy for the new dates. Select columns “M and N” and drag the bottom right corner of the cell over all the other cells. If previous months have passed and you want to remove them, you can select those months (columns), right click for the menu, and select Delete or press the Delete key.
You can also hide these columns by selecting the months (with the passed months), Click Format on the task bar, click columns, click Hide. Click Unhide to later view the columns.
Change the Numbers to Currency Format
Highlight the cells you want to change to currency format and click the “$” on
the task bar.
Change the saving by selecting B22 (cell) and then clicking the “$”
Adding Additional Sections
This can be accomplished so many ways.
1. Select Row 22 ( by clicking on the row column), click cut ( scissor icon) and pasta at row 28. 2. Type in cells:
a. A22, enter Donations b. B23, enter Red Cross c. B24, enter Churches d. B25, enter School
e. A26 enter Totals, right justify/Align this cell f. C23 and C25 enter 100
g. C24, select it, enter =C8*.10
h. C26, click to sum icon and then enter.
3. Copy Cells C23 to C26, to Columns D to Q, This can be done by using Auto Fill. 4. While these cell are highlighted (selected) click the “$” for currency format
5. Delete row C18 (church expense) from the Expense Section: Click on Row 18, right click mouse and click Delete
6. Budget Result Total = Saving + Expense Totals – Expense Totals – Donation Totals a. =B27+C8-C19-C25
Graphing Data
Graphing your data can be easily accomplished using the Chart Wizard. Charts consist of figures and labels. Select the data and the representing labels, we used B11 to C18. Afterward click the Chart Wizard icon on the Task Bar. It will ask:
Chart type
Select Pie
Select which type of Pie Chart via the pictures
Click Next on the bottom to proceed
Click in the name field and type January
Click Finish to complete a simple chart
This Chart will represent the expenses for the Month of January
Click on this Chart and drag it where you like it to be located. To Chart the month of February, simply hint column C and repeat the steps again.
Please Fill out the course evaluation, go on-line to:
Start your Web-browser and go to address:
www.artutorials.org
Basic Microsoft Excel Exercise
Lab Assignment is to create a Weekly Budget just like our Monthly above:
1. Select the Starting Cell and auto-fill copy
Use the directional arrow keys to move to different cell locations. Enter 01/07/2011 and 01/14/2010 in cells C5 and D5 respectively. Notice the point located at the lower right corner of the cell D5. Highlight cell C5 and D5, then drag this point across and to cell N5. The dates will be added continuously by weeks.
Cursor Movement: Press the tab key, Shift-Tab keys, and the Enter key
2. Entering Income and Expense Titles
Let’s start in cell A5 (not so important). Enter the titles Income, and sub-titles Salary1 in cell B6 and Salary2 in cell B7. Type Totals in cell A8.
Add the Expenses title in cell A10, the expense subtitles in B11, B12, and so on. Enter the Total title in cell A19. Example below:
3. Entering Expense Costs
Enter the estimated amounts for the salaries for only the first week. Also enter the costs for the expenses for only the first week, be as accurate as possible. Example Below:
Basic Microsoft Excel Exercise
Continue
….
4. Copy and Paste Technique
Because some months have more weeks than others, I recommend copying one week at a time. Copy the first week 01/07/2011, and paste this to 02/04/2011, 03/04/2011, etc
Highlight cells C6 to C18, click right mouse button, then select copy. Go to G6, right click right mouse button, then select paste. Repeat this copy and paste for each week to the other months.
5. Functions
Use the summation function to add the total incomes and expenses. In cell C8 type “=SUM(C6:C7) and in cell C19 type “=SUM(C11:C18)” .
Basic Microsoft Excel Exercise
Continue
….
6. Equations (
Balance = starting funds + income – expenses)
We need an equation to figure the balance at the end of each week. Enter the word ”Balance” in A21. Assuming you have $1000 starting, enter 1000 in B21. Enter in C21 the starting fund (B21) + Total Income (C8) – Total Expenses (C19). Equations must start with an “=” sign ( =B21+C8-C19).
Basic Microsoft Excel Exercise
Continue
….
7. Relative Copying
Copy the Total Income, Total Expenses and Balances from week 01/07/2011 to the other weeks. Click cell C8, click the right mouse button and select Copy. Click in cell D8 and drag across the other weeks (E8,F8,…), then press Enter. Repeat these steps for Total Expense C19 and Balance in C21.