by Glen Feechan
Now that Not Just Numbers has been running for a while, I thought that it might be useful to pull together a number of the Excel resources available on the blog and from the Feechan Consulting Group.
Regular readers will know that I am a big fan of Pivot Tables and you can learn how to set one up with the following videos:
Free Excel 2003 Pivot Tables Video
Free Excel 2007 Pivot Tables Video
If you want to learn about Pivot Tables in more depth, there are two video training courses available from Feechan Consulting:
Introduction to Pivot Tables course
Advanced Pivot Tables Course
Sometimes a pivot table is great for doing the number crunching, but you wish to use the results in a more flexible layout - for example, a management accounts format. To do this you will need the GETPIVOTDATA function:
Excel Tip: Using GETPIVOTDATA (part 1)
Excel Tip: Using GETPIVOTDATA (part 2)
Pivot tables come into their own when analysing large amounts of data, which can come from existing software packages:
Why all finance directors need to know about ODBC
Or be keyed in:
Using Excel Pivot Tables with manual records
Often you need to look up data from elsewhere within a spreadsheet, maybe to add new data to the source data for a pivot table. To do this you use the VLOOKUP function:
Excel Tip: Using VLOOKUP
Learn VLOOKUP with this short, free video tutorial
A couple of other useful tips with many applications are given below:
Excel Tip: Dropdown boxes in Microsoft Excel
Excel Tip: Reporting by year or month
Although I try to avoid macros where possible, sometimes you just need to automate a repetitive task:
Free Video: Record a Macro in Excel
A number of useful functions are discussed above, but here is a useful way to get to grips with an Excel function:
Excel Tip: Use any Excel function in seconds
For a useful application that ties many of these techniques together, have a read of:
Do your management accounts take weeks, days, hours, minutes…or seconds to prepare?
If you still don’t feel you are able to produce the spreadsheet you want, or you don’t have the time, why not get us to do it for you – quickly and cost-effectively with Spreadsheets by Email?
Or if your requirements are more complex, including reporting from your own systems, call Feechan Consulting.
If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates, our monthly ezine and your free report.