First Example done together: A budget, expenses sheet (part 2)
Here the next steps to our expense/budget sheet.
Go to the first page if you haven't read
it.
We do not need to write the formula on every line.... great!!!
By double click on the corner, the formula will be copied automatically
towards the bottom.
Now that we have entered the formulas, we can hide the unwanted columns,
right click on the columns and press Hide.
We have also changed the color of the header for monthly and yearly. So
that we can see them better.
An important point is that you want to see your header all the time. Also
when you have entered many many many expenses.... so lets do this by
freezing the rows (and columns if you want). By clicking on the A4 Cell and
then on the freeze pane button, we define the corner of the frozen zone
(brrrrrrr....)
Pivot Table and Charts
This are very important tools in Excel allowing you to sort,
and juggle with data.....REALLY...
We are going to create a Pivot table and chart. Select any
point in the table and then press the Pivot chart button
The create pivot table window opens and asks you to confirm
the table name (or range of the table sometimes). Press OK.
This new sheet opens. It will allow you to create a graph
just by dragging the fields.
By dragging the Month and Category into the AXIS area and
then the Expenses into the VALUE area you get already a table and a graph.
But... something is wrong with the graph. It show you the
COUNT and you do not want the count but the SUM of your expenses.
So by clicking on the small arrow to the right of the COUNT
OF EXPENSES, you will get the following window.
ANd by selecting the last one "VALUE FIELD SETTINGS" you get
to change the COUNT to SUM.
ET voila....
The graph and table are correct. Showing you per category
and per month your expenses.
If you add data in you entry table, then the Pivot table
will not refresh automatically. To refresh the chart, press ALT-F5.
Hope you enjoyed and learnt something....
Download the file here.