Transcript Excel

Excel
CREATING A WORKSHEET AND CHART
Personal Budget Worksheet
We will create a personal budget worksheet that shows you income each month and your
expenses. There are templates in Excel that we will explore later, but first we will learn to set up
a worksheet ourselves.
◦ If your Start screen contains an Excel 2013 tile or shortcut, click on that to start Excel.
◦ If you do not see Excel 2013, click on Search and type Excel to search for the program and click on Excel
2013.
◦ Click Blank Workbook on the Excel Start screen to create a blank workbook
◦ See next slide for screen shot of an Excel worksheet with explanations of the screen
Excel Worksheet
Each workbook (file) can contain
several worksheets. The blank
workbook starts with one (Sheet1)
worksheet
Rows are numbered. Columns are
lettered
Cell is referred to by column letter and
column number. Cursor is sitting in cell
A1
Entering Worksheet Titles
• Click cell A1 and
enter a title for the
worksheet
• Click cell A2 and
enter a subtitle
• Click the Enter
box in Formula Bar or
move to another cell
to complete the
entry
• Typing displays in cell
and in formula bar
Entering Column and Row Titles
COLUMN TITLES
ROW TITLES
•
Click cell A4 and type Wages
•
Click cell A5 or press the DOWN ARROW key and type
Interest/Dividends.
•
Click cell A6 and type Miscellaneous
• Click cell B3 or press the RIGHT ARROW key
and type Jan
•
Click cell A7 and type Total
•
Click cell A8 and type Expenses
• Click cell C3 and type Feb
•
Select the cells where the months are on row 3 and choose
Copy
• Repeat the previous steps until all months
are entered as shown on next screen.
•
Click on B8 and choose Paste
•
Click on A9 and type Mortgage/Rent
• Type Total at the end
•
Repeat the previous steps until row titles shown on next screen
are entered
• We usually add row and column titles to
clarify what data is in that column and row
• Click cell A3 and type Income
Text to Type on Worksheet
Resizing Columns
◦ Notice how text flows over into the next
cell until you type something in that next
cell.
◦ We will apply styles and formatting later
that will take care of column width, but
for now, let’s change the column width of
that first column.
◦ Double click between the column letters
A and B. That will do a “best fit”. The
column A will resize to the longest text
typed.
◦ You could also drag that line to the
desired width.
Entering Numbers
◦ Type the numbers shown for
January and February.
◦ If Excel sees numbers, it
automatically knows a
number has been entered.
However, if you type other
characters, then Excel will
assume the entry is text and
treat it accordingly
◦ If a number has no decimals,
then Excel simply displays the
number. We will format later
to make those show 2
decimals.
Formatting a Worksheet
Unformatted worksheet
We learn on the following
slides how to format this
Formatted worksheet
Using Cell Styles
◦ Click on cell A1. Choose the drop-down arrow beside styles on the Home ribbon. Choose the Title style
◦ Click on cell A2. Choose the Title style again.
◦ Resize rows – Drag Row 1 (the number 1 on the left of the screen) to about 32 points – or right-click on
that number 1 and choose Row Height and type 32.
◦ Make the font bold for cell A1 and A2.
◦ Merge and center the title. Select A1 – N1. Click on Merge and Center on the Home ribbon.
◦ Merge and center the subtitle. Select A2 – N2. Click on Merge and Center on the Home ribbon. Also
make the font smaller for this text.
◦ Shade both titles. Select A1 and A2. Click on the shading icon on the Home ribbon and choose the color
that says Blue, Accent 5, Lighter 60%. This name will display briefly as you move your mouse across the
colors.
◦ Make text on rows 3 and 8 use the Heading 3 cell style. Do not select the entire row – just select the
cells there that have the data.
More Formatting
◦
◦
◦
◦
◦
Select A7 – N7 and choose Total cell style
Select A25 – N25 and choose Total cell style
Select A26 – N26 and choose the light blue cell style (20% Accent 1)
Select the entire spreadsheet (the button shown here)
Choose Number formatting by clicking on the drop down
arrow beside General
◦ We usually use Currency format on the first
row of numbers in a part of the table and on
the totals row. Select B4-N4 and choose Currency
formatting either by clicking on the $ or by
clicking on the drop down arrow beside General.
◦ Do the same for B7-N7, B9-N9, B25-N25, B26-N26
Adding Formulas
◦ Summing numbers (Totals)
◦ Click on B7. Click on the AutoSum button. Make sure the cells automatically selected are the ones you want to sum. If not, then use
the mouse to select the ones you want to sum. Excel looks above the formula and highlights numbers it thinks you want to sum. If
it doesn’t find any, it looks to the left of the formula and finds numbers there. They are not always the ones you want. Click on the
check mark in the formula bar if those are the numbers you want to sum. Results will display in cell. Formula will display in cell.
Filling Across or Down
Now that you have the total correct, you can fill it across for the rest of the month. Click on the
small rectangle in the lower right corner of the cell. Cell pointer will change to a +. Drag across
through column N. The formula will copy and change for each column is moves to. In Feb, it will
add the income for Feb. In Mar, it will add the income for March and so on through column N.
Continue with the same instructions for the Expense totals in B25.
Creating Your Own Formula
We need to find out if you have money left over each month. We will subtract Total Expenses
from Income to determine the net.
◦ Click on cell B26
◦ Press the equals key. This notifies Excel that you a formula is coming and tells it to treat it as a formula
instead of plain text.
◦ Click on B7. This tells Excel to take the number in B7
◦ Press the minus key. This tells Excel to subtract.
◦ Click on B25. This tells Excel the number to subtract.
◦ Press the check box on the formula bar to record the formula. You will see the results in the cell and the
formula in the formula bar.
◦ Fill across through column N as you did before
◦ If you see #### in a cell, that means the cell is not large enough to hold the results of the formula. Just
increase the cell width by dragging or by right-clicking on the cell and choosing Cell Width and typing a
new width
Filling Formulas Down
Let’s sum the Totals for everything for the year.
◦ Click on N4.
◦ Click on the AutoSum button.
◦ Make sure the cells automatically selected are the ones you want to sum. Since there are no numbers
above cell N4, Excel looks to the left of the formula and finds numbers there. They are not always the
ones you want.
◦ Click on the check mark in the formula bar if those are the numbers you want to sum. Results will
display in cell. Formula will display in cell.
◦ Fill down
◦ Click on the small rectangle in the lower right corner of the cell N4. Cell pointer will change to a +. Drag down through row 6. The
formula will copy and change for each of the income rows is moves to.
◦ Click in cell N9 and sum that row. Fill down through row 24
◦ Reformatting number styles
◦ Notice that when we copied it put the currency style on every total. That looks a little cluttered, so select the ones in the middle
areas and change the number format back to Number.
What If???
Spreadsheets are meant to say What If. What if I want to purchase a new house and the
mortgage goes up. What if it is December and I spend much more on gifts? You simply enter in
estimated numbers for a month and the formulas will automatically update. If you were just
testing and playing What If – then you would just delete those numbers you typed and the
formulas will go back to the current year. Or – you could close the file without saving.
Charts
Charts show the data visually. You select what you want to chart and then click on Insert / Chart.
Choose the type of chart you want. You always want the text to tell you what is displayed in the
chart, so always choose the appropriate text. In our case, let’s chart the Yearly Expenses. Since
the Expense titles are in column A and the total expenses are in Column N, we cannot simply
drag across. We select the expenses in column A. Hold down the CTRL key and select the
corresponding totals in column N.
Formatting and Placing the Chart
◦ Select Pie / 3-D Pie
◦ There are many styles of the 3-D chart. You can select different style if you want. In our case, there are
many expenses, so the default style works best.
◦ Click on Chart Title (that is a placeholder for you to type in the title). Type Yearly Expenses. Then press
enter.
◦ Click so the cell pointer is a 4-headed arrow and move the chart to the bottom of the spreadsheet. You
will have to scroll down some to be able to move it.
Putting a Chart on a New Worksheet
◦ Select the data again. Select the Expenses and the corresponding totals. Remember to hold down the
CTRL key since the columns are not next to each other.
◦ Insert / Chart – but choose a bar chart and choose 3-column bar chart.
◦ Once it creates, right-click on the chart and choose Move Chart. Choose New Sheet and type in the
sheet tab name to use. Let’s use Yearly Expenses
◦ The new chart will fill an entire worksheet. You have two worksheets now: Sheet 1 (the actual
worksheet) and Yearly Expenses (with the bar chart)
◦ This one can benefit from another style.
Click on Design under Chart Tools. I chose
Style 6 from the chart styles shown there.
◦ Click on the placeholder for the title and
type Yearly Expenses
Renaming Tabs
We now have Sheet 1 and Yearly Expense worksheets in our file. Rename the Sheet 1 tab to
Monthly Income and Expenses. Right-click on the Sheet1 tab and choose Rename. Type in the
new name.
Status Bar
The Status Bar at the bottom of the worksheet contains information about whatever you have
highlighted. See how I have selected a group of cells. In the Status Bar, it shows the Average,
Count, Maximum and Sum of those cells highlighted. It is a great way to look some totals or
averages quickly. You can customize the Status Bar by right-clicking on it and choosing what you
want to display.