Another powerful feature of Excel is the ability to create graphs and charts quickly from your data. We are now going to create a bar graph to do just this.
Creating a chart to display our summary:
| Screenshot | Task Description |
|---|---|
| First click on the cell where you would like the chart to be placed. You will be able to move the chart at any time, so it really does not matter. From the Insert menu, select Chart to start the Chart Wizard. | |
| Select the chart type. Remember we have a negative value, so we cannot use a pie chart. In this example I am going to use a bar chart. Select Bar from the left column, the chart design which best catches your eye from the sub chart list (Clustered Bar works best for what we are doing), then click Next. | |
| Now we need to tell Excel where the data for the chart is located. We are going to select this directly from our summary sheet, so click the cell select button located on the far right of the Data Range input box. | |
The Chart Wizard now compresses to a small dialog and we see our Summary sheet. Highlight the range A2 to B4 so there is a dotted line box around the cells. Notice this range is automatically input into the Chart Wizard. Click the button on the end of the Data Range to expand the Chart Wizard. Notice the $ symbols. Remember what this means? | |
| You should now see a preview of the chart in the Chart Wizard. Make sure you have the series option set to Columns to tell Excel that our descriptions and data are on separate columns as opposed to separate rows. Click Next. | |
| Next we are going to configure the way the chart is displayed. Click the Titles tab and enter a title for the graph as well as a description for our X and Y axis. | |
| On the Legend tab, unselect the option to display the legend. Since the data is pretty straight-forward, there is no need to waste space on this. Click Next. | |
| In the final step of the Chart Wizard, we need to tell Excel where to place our new chart. In my example, I am going to insert it in the Summary tab and click Finish. | |
| Finally, our chart is placed in our summary sheet. Resize and move it where ever you like. |
The great thing about the chart is it will update whenever our summary values change. So an example of the trickle down effect would be like so:
- Credit card charge of $100 is made
- Our current balance on the Credit Card sheet is updated
- Our Credit Card balance on the Summary sheet is updated
- Our Total balance on the Summary sheet is updated
- Our balance chart is updated
All this happens because we have cell formulas and links which update each other.
You can download financial tracking workbook up to the point where the walkthrough completed by clicking here. Please use it and expand upon it. The possibilities are endless, for example you could:
- Track mortgages and loans
- Categorize your spending
- Create pie charts (for example, one for “assets” and one for “liabilities”)
- Password protect your data (Tools > Protection > Protect Workbook)
- Anything else you can imagine
I hope this article is given you a taste of what Excel can do. The examples we went through are quite barebones and I encourage you to build upon them to truly utilize the power Excel offers. If you have questions or are trying to figure out something, remember to use the Excel documentation available through the help menu, additionally there are numerous other Excel resources available simply by doing a Google search.
As always, I welcome any feedback so please let me know what you thought about this article.
The PCMech.com weekly newsletter has been running strong for over 8 years. Sign up to get tech news, updates and exclusive content - right in your inbox. Also get (several) free gifts.
