Search Not Just Numbers

Loading...
Showing posts with label Pivot Tables. Show all posts
Showing posts with label Pivot Tables. Show all posts

Tuesday, 26 March 2013

Excel Tip: Presenting Pivot Table data in a fixed layout using GETPIVOTDATA

It's been a while since I covered GETPIVOTDATA on the blog, so I thought it was worth a re-visit, but with the specific objective of demonstrating a structured approach to populating a fixed layout report, with data pulled from a pivot table.

NB: This post assumes that you already have, or know how to create, a pivot table containing your data. If not, you might find my video training course on the subject useful.

A very common example of this type of application is monthly management accounts: A pivot table can be great for doing the analysis and producing the numbers for the accounts, but these usually then need to be presented in a particular monthly accounts pack template.

This post will explain how to populate any fixed report with data pulled from a pivot table. This can be a great way of automatically populating any existing report that you might currently be populating manually.

One question that I am sure some readers will ask is "Why not use SUMIF and skip the pivot table altogether?"

This is a fair question and I sometimes take that approach, however one big advantage of the pivot table step is that you get to see ALL of the data summarised and can identify any data that is not appearing on the final report - SUMIFs on the other hand will only show what you look for, but you will not see any new categorisations that are not on the final layout (for example, a new nominal ledger code). Also, it is much easier to trace how a number is made up using the pivot table approach.

Let us take the pivot table report we produced in my cash book post:


NB: I have made one slight tweak to the one produced in that post, in that I have moved the year and month to be columns rather than report filter fields. This means that all years and months will appear as data is added, and our final report can pull out the month required.

The final template we use looks like this, and we normally key in the numbers from the pivot table (in reality, this might be a 30-page management accounts pack, but the principles are just the same as in our simple example):



Our objective is to automatically populate this in a structured way, from the pivot table.

We need to do a little preparation on the final report, by inserting 2 columns to the left of the report (these can be hidden, or kept outside of the print area, once we are finished).

In the first column, on the rows we need to populate, we enter exactly how that row is described in the pivot table. In the second column, we enter a 1 or a -1, depending whether we want to reverse the sign on that row or not.

So the report sheet now looks like this:



We will now build a formula that can be copied into every cell that we want to pull data into. We will create the formula in cell F5 and copy it to the others (NB: The totals are all just simple sums and are already in the template).

If you remember from my earlier posts on GETPIVOTDATA, the syntax of the function is as follows:

=GETPIVOTDATA("Name of data field to return",location of pivot table,"field1","item1","field2","item2")
Where:
Name of data field to return is a text field, being the name of the pivot table field as it appears on the pivot table.
location of pivot table is a reference to a cell anywhere within the pivot table, it is often best to use the top left cell of the main body of the pivot table, as this will remain in a constant position no matter how much the data changes.
field1, item1,field2,item2,etc. are pairs of field and item names from the row, column or page (filter) areas of the pivot table. You can use as many pairs as you like. The field name comes from the original column heading in the data, the item name is the entry in that column that creates a unique row, column or page in the pivot table.


SO to return the figure we want, being the commission sales figure for month 11 of the year 2012, the GETPIVOTDATA formula would be:

=GETPIVOTDATA("Value",'Income and Expenditure'!$A$4,"Expense/Income Name","Consultancy Sales","Year",2012,"Month",11)

i.e. return the total of the field Value, from the Pivot Table at 'Income and Expenditure'!$A$4 where Expense/Income Name is "Consultancy", Year is 2012 and Month is 11.

For this formula to be able to be used throughout we need to change the values to references.

We can swap "Consultancy Sales" for $A5 (notice we place a dollar sign before the A, so that if we copy the formula to a different column, it will still look in column A for the Expense/Income Name).

We can swap 2012 for $F$2 (here we have used the dollar signs to fix both the row and the column as the year will always be in cell F2).

Finally we can swap 11 for $F$3.

So our new formula is:

=GETPIVOTDATA("Value",'Income and Expenditure'!$A$4,"Expense/Income Name",$A5,"Year",$F$2,"Month",$F$3)

There are a couple of additional changes that we need to make to complete the formula before we copy it to the other cells.

This formula works great if there is a figure in the pivot table for the year, month and category in question, but will return an error otherwise, so we need to catch that and return a zero. I covered this in a post a couple of years ago (EXCEL TIP: Eliminating #DIV/0! and other errors automatically using ISERROR). In later versions of Excel, you can use IFERROR to replace the two functions IF and ISERROR, however I don't see that this makes the formula any simpler, yet stops it working in earlier versions, so we will stick to IF and ISERROR here. The new formula is now:

=IF(ISERROR(GETPIVOTDATA("Value",'Income and Expenditure'!$A$4,"Expense/Income Name",$A5,"Year",$F$2,"Month",$F$3)),0,GETPIVOTDATA("Value",'Income and Expenditure'!$A$4,"Expense/Income Name",$A5,"Year",$F$2,"Month",$F$3))

Finally, we need to reflect the column we added to allow us to reverse the sign. In our example, the pivot table has sales (credits) as a positive and expenses (debits) as a negative (if this had come from a traditional trial balance, they would have been the other way around). In our final report, we want to show both as positives and the totals will deduct expenses from sales, so we have entered 1 against the rows that we want to keep with the same sign and -1 against the expenses, where we want to reverse the sign.

This is simple to reflect in the formula as we just need to multiply the result by column B:

=IF(ISERROR(GETPIVOTDATA("Value",'Income and Expenditure'!$A$4,"Expense/Income Name",$A5,"Year",$F$2,"Month",$F$3)),0,GETPIVOTDATA("Value",'Income and Expenditure'!$A$4,"Expense/Income Name",$A5,"Year",$F$2,"Month",$F$3))*$B5

Note that we have again used the dollar sign to fix the column, but not the row.

We can now copy this formula into cells F6 and E10 to E13.

Our final report now looks like this:


The numbers will now all automatically update if the pivot table changes or if the year and/or month are changed at the top.

This approach works with far more complex reports than this and can be expanded to allow for multiple value fields in the pivot and multiple columns in the final report.

I use this approach regularly to automatically populate existing reports for clients. They often have very elaborate reports that take ages to complete manually. With this approach, I can insert a couple of columns and work out the formula, without changing the look of their report at all.

Go on, revisit all of those manual reports now. You'll be glad you did when next month end comes around!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Tuesday, 29 January 2013

Excel Tip: How to combine multiple sets of data for a pivot table - a practical solution

I have written many posts about the benefits of pivot tables and how to prepare data for use in them, but there is one scenario that I haven't covered which I come up against every now and again with clients. What if we have multiple sets of similar data that we wish to report across using a pivot table?

An example might be where we are comparing sales reports at a group level and are provided with a list of sales invoices from each of three departments or subsidiaries. To complicate matters further, the three departments use different invoicing systems and therefore the lists are in different formats.

Excel has a facility to consolidate ranges in a pivot table but this is for a very specific scenario and I, personally, have yet to find a practical use for this. It certainly does not help us here.

The good news though, is that I have developed a way of approaching this problem that works every time, and is pretty straight-forward.

To include them all in one pivot table, we will need them all in one list with one set of headings. And we want to do this in such a way that the work is already done for next month.

To start with we import, or paste, the three lists into their own individual tabs within our workbook.

We then set up a fourth tab and enter the column headings that we want to use for our consolidated table. These might be Date, Invoice Number, Salesperson, Customer, Amount. We also add an extra column for Department.

We want to allocate a section of the consolidated list to each of these departments.

Before we do that, we need to look at the typical size of each of these lists. Say that each is typically 2,000 rows - we might want to allocate 10,000 rows to each department to allow plenty of slack.

We now populate the first row of the consolidated sheet as follows.

In cell A2 (the first cell in the Date column), we enter a formula to pull the date from the first row of department 1. In cell B2 (the first cell in the Invoice Number column), we enter a formula to pull the Invoice Number from the first row of department 1, etc.

So, if department 1's data is held on a sheet called Department1 and the date is in column C and the Invoice Number is in column F, then on the consolidated sheet, cell A2 contains

=Department1!C2

and cell B2 contains

=Department1!F2

when this has been done for each of the columns we then enter the name of the department in the department column and copy the whole row down all the way to row 10,000.

Then in row 10,001 we do the same for the first row of department 2 (obviously referring to the correct columns for department 2 as these may be different. We then copy this down to row 20,000 and repeat again for department 3 on row 20,001.

We can then use these 30,000 rows as the source range the pivot table, allowing us to report across the departments, and by department thanks to the department column we added.

Next month we just have to import or paste the new data over the old data for each the three departments and the consolidated list will automatically update.

If you don't know how to create the pivot table itself, take a look at my earlier post, or you can purchase my Introduction to Pivot Tables video course here. If you're quick and do this by 31st January 2013, you can get it for half price.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Tuesday, 22 January 2013

Why do I need pivot tables? - and a song!

Before we get to the song, I want to answer a question I am asked over and over again:

"Why do I need pivot tables?"

Regular readers will know what a big fan I am of pivot tables, and how they can transform how you use Excel. However the biggest obstacle that people seem to face is not that pivot tables are complex, but that they don't understand what they are for.

In short, Pivot tables allow you to analyse and summarise large amounts of data quickly, easily and incredibly flexibly (if that's  word!).

Quickly:

It can take seconds to transform tens of thousands of rows of data into a summary table, displaying the important information you need to see.

For example: from a list of sales invoices pulled from your accounting system, you can create a 12 month sales summary by customer with a column for each month. That should typically take less than a minute to create from scratch.

Easily:

Building pivot tables is all done with wizards and the layout is built visually using drag and drop - no complicated coding just a simple way to analyse your data.

Flexibly (if it's not a word, it should be):

In the sales summary example above, what if you (or your boss) now wants to see the same sales summary by salesperson and region, rather than by customer. This can be done in about 20 seconds. Just think if you'd built that summary without pivot tables - how long would it take to change it?

If I've convinced you that you need to know more about pivot tables, you can still take advantage of my January deal if you're quick. Just click here and you can have mastered pivot tables within the next hour.

Or alternatively, maybe these boys can convince you with a song:




If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Wednesday, 11 April 2012

Use Excel to generate invoices and report on sales

I come across many small businesses who use Excel (or Word) to produce an invoice template that they can email or print and post to their customers.

Usually the sale detail is entered on the face of the template and this is saved as a separate file for each invoice. The problem here is that to be able to report on this information for accounting purposes, or sales analysis, the information needs to be recorded somewhere else in one file (either another spreadsheet, or an accounting software package).

What is really needed is to be able to enter the sale details once and for this to be used to populate both the invoice template and the sales reports.

If an accounting package is being used, the best answer would usually be to use an invoicing module in the accounting software and ditch the external template, however if this is not an option, or an accounting system isn't used, then we need the invoice template and the sales history to be linked.

Here is my approach to this problem, in the simple scenario where each invoice has one line of detail. If more lines are required a variation of this approach can be used, but this is a little too complicated to go into in this blog post.

General Approach
This approach involves three main worksheets within the one spreadsheet, Customer Data, Sales Data (what accountants often call a Sales Day Book) and the Invoice Template.

Customer Data
This worksheet, as the name suggests, is where all of the customer data is held. The sheet should follow the rules for laying out data that I outlined in an earlier post (How to lay out data in Excel). It should have column headings for name, address 1, address 2, etc. and one row for each customer, new customers are just added to the bottom of the list as needed.

The first column should be a unique identifier for that customer, it may be possible to use the name for this but you would need to add a new customer on a change of name rather than editing the existing one. Often better is to use the approach used by most accounting systems and allocate each customer a unique Account Code. I often use Conditional Formatting on this column to identify any duplicates.

The spreadsheet will use this sheet to provide the customer address details for the invoice.

Sales Data (or Sales Day Book)
This sheet should again follow the rules mentioned above. Here the business will record all sales, one line per sale. Headings should include (at least) Invoice No (in the first column), Date, Amount, Customer Account Code (the unique identifier mentioned in Customer Data above) and Description. Depending on needs the amount may be a calculated column, multiplying entries in columns for price and quantity. The business may also need a column for VAT or any other sales tax. The Customer Account Code column should use Data Validation to restrict entries to those codes in the first column of the Customer Data sheet.

The VLOOKUP function can then be used to pull in the customer name and address details in columns to the right of the entered data, using the customer account code to look that information up from the Customer sheet.


Invoice Template
If the business is already using an Excel template then this can be used as the basis of this sheet, otherwise some work will need to be done to produce a satisfactory layout for the invoice.

Ultimately there should only be one editable cell on these sheet - the Invoice Number which should be a dropdown based on the Invoice Number column of the Sales Data sheet.

VLOOKUP is then used on the rest of the invoice to pull the Customer Name and Address, Date, Description, Amount, etc. into the appropriate cells from the Sales Data sheet, based upon the Invoice Number selected.

I would recommend that to email the invoice to a customer, that it is printed to pdf first. This not only gets round the problem that all of your other sales data is in the same spreadsheet, but looks far more professional than sending a spreadsheet to the customer.

Reporting
As the sales data is all correctly laid out and, depending on the size of the business, at least a year's worth of sales can be stored in the one spreadsheet (usually many more) then Pivot Tables can be used to report on these sales in any way that is required.

Just don't forget to make sure you take regular backups and there is no need to record your sales anywhere else.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Friday, 2 March 2012

Adding categories to data in Excel - how to save you time later

In my previous post, I stated that one of the major problems with how most Excel users lay out their data, is using a column for each category.

In the feedback I have had from that post, it was felt that this point needed further explanation and/or an example, so I thought I would provide both here.

First of all, here is the point as it appeared in the original post (it was point number 3):

Don't group data by putting it in different columns (THIS IS THE ONE THAT ALMOST EVERYONE GETS WRONG)
    • Don't split out financial or numerical data into separate columns to categorise the data into months, expense categories, customers, agents, etc.
    • Do have one column for the financial or numerical data and create a column for month, expense category, customer or agent, to categorise each row;
    • You can use data validation drop-down lists to select the appropriate category for each row;
    • This one is counter-intuitive because in any report, you will almost certainly will want a column (or row) for each of these categories - but if you do this in the data you will massively restrict what you can do with it.
Let's look at some sales data laid out the wrong way:



I have left out any extra data (other than the date) to keep it simple. With the data laid out like this, you could use the SUM function to calculate monthly totals, but you can't do a lot more than that. If you were to use the data in a pivot table, you would have to add the data as 12 data fields, making it very cumbersome and inflexible.

Also, if you wanted to do any calculations on this data, such as calculating VAT, or any other Sales Tax for that matter, you would need another 12 calculated columns!

You then get into further problems if you want to analyse the data from another perspective - by salesperson for example.

A better approach 

Now let's see a better way to lay this data out:

You can also automate the month column using the following formula in cell C2:

=CHOOSE(MONTH($A2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

Laid out like this you can use the month as a way of analysing the amount in a pivot table for example:
Its real power however comes when you wish to add additional analysis and calculations, so for example you could add additional analysis columns for Salesperson and Country, and a VAT column (being 20% of amount):

You can then produce all manner of pivot tables, here are just a few examples:

It would be just as easy to show the months as columns - the only reason I haven't is to make best use of the space.

And remember, you can at any stage easily add further analysis or calculation columns as your reporting needs change.

I hope this has explained this point in more detail and even more importantly, highlighted the value of getting it right!


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Thursday, 23 February 2012

99% of Excel users get this wrong - How do you lay out your data?

"Learn the fundamentals of the game and stick to them. Band-Aid remedies never last."
Jack Nicklaus (Champion US golfer) 



When someone comes to me with a problem in an existing spreadsheet, the problem is invariably in the layout of the data. The spreadsheet is built for one purpose and works OK for that until something slightly different is required and it proves almost impossible to get the report that's needed.

If a few simple rules are followed when laying out your data, then producing additional reports from that data, and using it for different purposes, becomes simple, instead of the nightmare it is for many users.

These rules apply to any lists of data, be it monthly financial information, transactional data (such as lists of sales, purchases, payments or receipts), customer or supplier lists. If you are going to store data in your spreadsheet to produce reports from, you need to follow these rules.

At the heart of these rules is the approach - you are not laying out your final report here, you are laying out the data in a format that can be reported from! These are two very different things (see my OAP approach to reporting in Excel).

The rules to follow:

  1. Columns with headings and no gaps
    • Every column should have its own UNIQUE heading, in the first row;
    • There should be no empty columns;
    • These columns represent the fields of a database, e.g. Customer Code, Customer Name, Telephone Number, Email Address, etc.
  2. One row per record and no gaps
    • Every record should have all of its data on one row. E.g. in the above example, one row per customer;
    • There should be no empty rows;
  3. Don't group data by putting it in different columns (THIS IS THE ONE THAT ALMOST EVERYONE GETS WRONG)
    • Don't split out financial or numerical data into separate columns to categorise the data into months, expense categories, customers, agents, etc.
    • Do have one column for the financial or numerical data and create a column for month, expense category, customer or agent, to categorise each row;
    • You can use data validation drop-down lists to select the appropriate category for each row;
    • This one is counter-intuitive because in any report, you will almost certainly will want a column (or row) for each of these categories - but if you do this in the data you will massively restrict what you can do with it.
The benefits:
  • Data following the rules above is perfectly prepared to be analysed using countless tools within Excel, for example: pivot tables, autofilter, SUMIF, COUNTIF, etc.
  • Most changes to the data don't require a change to the data layout. New categories, e.g. expense categories, customers, agents, etc. can just be added to the drop-down lists. Any new entries in these columns will be automatically picked up by pivot-tables, autofilter, etc. with no work involved.If you had to create a new column each time, you would also need to edit every report that used the data.
  • You can choose to analyse the data by any category you want. It takes seconds to edit a pivot table that has a column for each month and change it to a column for each expense category. This is almost impossible if the data was laid out in those columns.
  • You can add additional category columns to the data if needed and these can even be calculated from the data. You might, for example, introduce departments - simply add a department column to the raw data, and your pivot tables can analyse the data by this category as well, or instead of existing categories.
As you can see, if you lay out your data according to these rules, you can do pretty much anything you want with it. The spreadsheet can grow with your business, and with any additional reporting requirements you want to add.

It can take a little bit of time to get your head around point 3, but believe me, you'll be pleased you decided to be among the 1% that get this right.

If you'd prefer me to redesign your spreadsheet for you, just visit www.needaspreadsheet.com and let me know what you need and I will send you a fixed price quote.


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Thursday, 18 February 2010

Become a Pivot Table Expert and save




Apologies for a blatantly promotional post, but I think many of you might find it very useful.




To celebrate the 1st birthday of Spreadsheets by Email I am making a very special offer on our Introduction to Pivot Tables course.


Purchase the Introduction to Pivot Tables video course on or before 25th February and receive a massive 50% discount on the purchase price.


But that's not all - Purchase the course before this date and you will receive a voucher for 20% off any spreadsheet you want built at Spreadsheets by Email.

So, what better time than right now to become a Pivot Table Expert.

End of blatant promotionalism.



If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report.

Tuesday, 9 February 2010

Top 5 Tips to make Excel Spreadsheets work for you




I thought I would share with you an article I was kindly asked to write for Malcolm Gallagher's Achiever Newsletter. It's a nice summary of what I think are the top 5 most useful things to learn to get the most out of Excel. I'd also love to hear any you feel I have missed (please use the comments below).

Here is the article:

Top 5 Tips to make Excel Spreadsheets work for you

You probably already use Excel in your business to some extent, but very few businesses even scratch the surface of what it can do for them. I build spreadsheets for businesses throughout the world and below I will show you the top 5 things you should know to really start making Excel work for your business:

1.      Learn Pivot Tables - The number one spot has to go to learning how to create a Pivot Table. A pivot table allows you to analyse any data you have, whether you have keyed it in or pulled it from a database such as your accounting software, in seconds. Master these and you will amaze yourself with how you can master your data. There is not enough space here to show you how to do this, but here is a FREE VIDEO that will take you through it step-by-step.
2.      Find the Insert Function (fx) button - This little tip pushed pivot tables close for the number one spot because it allows you to use any function Excel has, without knowing it first. All you need to do is click the little fx button to the left of the formula bar and you can browse all of Excel’s formulae. It will tell you what the formula does, how to use it and then help you to fill in the arguments step-by-step. To see how it does this, just click on it, or see this blog post for more detail.
3.      Use dropdowns for data entry – The information you get from a spreadsheet is only as good as the data you (or your employees) put in. It is very easy to use Excel’s Data Validation to create a drop-down list that only allows you to enter data from that list. I have created a blog post that covers how to do this in detail.
4.      Learn the VLOOKUP formula – You can access this via the Insert Function (fx) facility discussed in point 2. VLOOKUP allows you to look up data from a list in a spreadsheet. For example, you could enter a product code and automatically show the description and price from a separate price list. Use the fx button for more detail or watch the FREE VIDEO on my blog.
5.      Learn how to record a macro – a macro is a short program attached to a spreadsheet that carries out a particular task. The good news is that you do not need to be a programmer to create one – when you have a repetitive task you can simply ask Excel to record you carrying out the task and create a macro to do it automatically next time. You do this by selecting Tools > Macro > Record New Macro… Again, there is a short FREE VIDEO on my blog.
Hopefully these tips help you to tap into how Excel can help your business, just question whenever you are manually manipulating data whether it can be done in Excel – it almost certainly can.
For those of you who don’t have the time, or the inclination, to build the spreadsheet you need, why not let my team and I do it for you at Spreadsheets by Email?



If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Monday, 16 November 2009

Excel Pivot Table Uses: Cash Book Analysis


Regular readers will know that I am a huge fan of Pivot Tables in Excel and I thought it might be useful to start an occasional series highlighting some of their uses. I thought I would start with an accountant's staple - Cash Book Analysis.


If you are not yet familiar with Pivot Tables, it will almost certainly help to view our free video on the subject before you read the rest of this article. Alternatively, if you have already seen this and want to learn pivot tables in greater depth, have a look at our Introduction to Pivot Tables video training course.

Typically, accountants use Excel to automate a traditional cash book by mimicking its manual equivalent with the added benefit of the totals being automatically calculated. A slightly different approach, using the power of Pivot Tables, reduces the data input and significantly increases the reporting flexibility.

Instead of replicating the net balance of each cash transaction in the column associated with its category (as in a manual cash book), simply have a column that contains the name of that category (this can be a drop-down list using data validation) and include the net value once.

By dragging this field into the column area of the pivot table, with the net amount as data, you replicate the traditional approach - but can choose to just show totals rather than the detailed transactions (if you wish). Using the "=month()" formula, you can have a column alongside the data that strips the month number from the date. If you use this as a the Page field in the Pivot Table you can provide monthly summaries.



If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Thursday, 12 November 2009

New location for free Pivot Tables Videos

Many of our regular readers will have already viewed our Free Excel Pivot Table Training Videos for both Excel 2003 and 2007, or indeed have purchased the full Introduction to Pivot Tables Video Training Package.

Due to their popularity these have now been given their own domain at www.pivot-tables.biz.

You can still see the free videos at www.pivot-tables.biz/FreeVideo.htm or purchase the full course at www.pivot-tables.biz.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report.

Thursday, 8 October 2009

Is it Pivot Tables or Age that is puggling my mind?

Earlier today, I told a client I didn't want a late finish next Friday because it was my birthday. I then put the telephone down and it wasn't until about 20 minutes later that I realised that my birthday is tomorrow!

I have narrowed the cause of this bumbling behaviour down to two possibilities:

  1. Naturally age is a contender, always front of mind when another year passes, but one would would hope that senility was still a while away at 39;
  2. Excessive exposure to Pivot Tables over the last 10 years or so.

My money's on the latter. I'd appreciate any of your confessions of your own bumbling in the comments below both to make me feel better, and to give us all a laugh on a Friday!


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report.

Saturday, 1 August 2009

Excel Resources for Accountants

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.

Tuesday, 21 April 2009

Excel Tip: Using GETPIVOTDATA (part 2)

This post continues from where we left in Excel Tip: Using GETPIVOTDATA (part 1) . We had explained the construction of the GETPIVOTDATA formula and how to use it to report a particular figure from the pivot table.

In this post we will look at the flexibility that can be achieved by using formulae to populate some of the arguments in the GETPIVOTDATA function.

To recap, the GETPIVOTDATA function has the following format:

=GETPIVOTDATA("Name of data field to return",location of pivot table,"field1","item1","field2","item2")

but each of the text fields (in inverted commas) can be replaced by formulae which, used intelligently, allow you to produce management accounts and reports in any format.

Examples:

You could replace "Name of data field to return" with a reference to the cell at the top of the column in your final report, which can hold the name of the data field you wish to return. By using [F4] to insert a '$' in front of the row but not the column in this reference, you can copy the formula across multiple columns using different data fields from the pivot table in each column on your final report. This is useful for reporting, say, month and year-to-date figures in management accounts.

Where the field item refers to the name of the raw data column containing a code that determines where the values go in your final report (e.g. a management accounts code) - if you replace the corresponding "item" field with a formula referring to the first column of the spreadsheet, this can be used to populate the rows of your final report. By using [F4] to insert a '$' before the column and not the row, you can now copy the same formula into every cell in your management accounts. Each row will show the value for that row, based on the code in the first column and each column will include the data field to use for the value, based on the first row.

How you get theses management accounts codes into the raw data is covered in an earlier post:

Do your management accounts take weeks, days, hours, minutes…or seconds to prepare?

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Golden Cells: The ExcelZone online video awards

It is always nice to get recognition for what you are doing and I was pleased to see our Introduction to Pivot Tables video course featured in Accountingweb's (a respected UK Accounting website) Excelzone online video awards. The video was described thus:


"Many of the Excel video pioneers are American, but a new generation of UK
producers has emerged in recent months led by Carlisle-based Emily Coltman. Her
four-minute
Introduction to pivot table course video for
Feechan Consulting truly is a state-of-the art production, featuring shiny pink
introductory graphics, slick page-folding transitions and the now obligatory
enlarged yellow spotlight cursor technique. The content and pacing are well
planned and the narration crisp and authoritative.


You can see the free video here.

Thursday, 16 April 2009

Advanced Management Reporting in Excel, London,20th May 2009

You know that you have all of the information you need in your accounting and management systems, however every day, week and month you (and your team) spend time pulling it from different sources, tweaking it and presenting it in a format that you (and the rest of the management team) can use.

What if you had the skills to automatically populate your existing spreadsheets and/or create new ones that give you all of the information you need, in a few clicks, when you want it. You can reduce time spent on management accounts preparation, for example, from days to minutes or even seconds.

This one-day workshop is a rare opportunity to learn my unique approach to automating all of your reports (from simple sales reports to complex management accounts). This is possible, no matter what accounting and management software you use in your business.

For just £275 plus VAT per delegate, you will learn how to:
  • Interrogate your existing systems
  • Analyse the resulting data using lookups and pivot tables
  • Present it in any format, including your existing spreadsheets (no matter how complex), automatically

Click here to find out more and/or to book online.

Excel Tip: Using GETPIVOTDATA (part 1)

As regular readers will know, I am a huge fan of pivot tables and find them an incredibly useful and powerful feature of Excel. If you don't know how to create a Pivot Table, take a look at my free video.

For all of the power of Pivot Tables, sometimes the final format is not want you want and you need to use that data elsewhere. Management Accounts are a prime example of this - a pivot table can give you the numbers but you usually want these to then populate an ordinary Excel template, set out in your Management Accounts format. This is where the GETPIVOTDATA formula comes in.
GETPIVOTDATA allows you to specify the data you want to pull from the Pivot Table. The format is as follows:
=GETPIVOTDATA("Name of data field to return",location of pivot table,"field1","item1","field2","item2")
Where:
Name of data field to return is a text field, being the name of the pivot table field as it appears on the pivot table.
location of pivot table is a reference to a cell anywhere within the pivot table, it is often best to use the top left cell of the main body of the pivot table, as this will remain in a constant position no matter how much the data changes.
field1, item1,field2,item2,etc. are pairs of field and item names from the row, column or page (filter) areas of the pivot table. You can use as many pairs as you like. The field name comes from the original column heading in the data, the item name is the entry in that column that creates a unique row, column or page in the pivot table.
I think an example might be useful here:


To return the figure 15,813 for Matthew's sales for the South East, the formula would be:
=GETPIVOTDATA("Sum of Net",A3,"Salesman","Matthew","Region","South East")
This is useful when you are wanting to report one number, however you can really make use of this formula when you realise that any of the text arguments (in inverted commas) can be replaced with references to cells where the text can be found. You can then use this to populate any report layout, but I will leave this for a later post.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Tuesday, 24 March 2009

Excel Tip: Reporting by year or month

Some very useful functions in Excel, particularly when reporting on transactional data, are the following date functions:

Year( ), Month( ) and Day( )

For each of these functions, you need only put the cell reference of a date field between the brackets and it returns the year, month (1-12) or day (1-31).

Using Year( ) and Month( ) in particular, you can add additional columns alongside a list of sales or other transactions to strip the year and month from the date column. This allows you to report by these in a pivot table or a simple Autofilter.

Tuesday, 17 February 2009

Free Excel 2007 Pivot Tables Video

Here is a sneak preview of my Introduction to Pivot Tables course, showing you how to create a pivot table in Excel 2007.

You can download the whole course for both Excel 2007 and Excel 2003, including manual, sample data and real world examples here.

This free video is now available here.

This video just scratches the surface. You can find out more and get the full course (in Excel 2003 and 2007 versions) here.

Friday, 13 February 2009

Excel 2007 Pivot Tables Training Videos

My Introduction to Pivot Tables video training package has now been completely rewritten for Excel 2007, and what's more it is still available for the same low price and includes the Excel 2003 version free of charge.

Here is what a purchaser of the Excel 2003 version had to say:

"I bought a book on pivot tables from Amazon a number of years ago and I read 3 chapters but never obtained any knowledge on how to use pivot tables. Whereas with your video you were immediately presented with the knowledge of how to prepare pivot tables......Again many thanks for creating your presentation on pivot tables I certainly think it is very informative and well worth the money."

To immediately download your own copy for use throughout your organisation, click here.