You are here:

Excel/Consolidating categories of expenditure into separate sheets



My spreadsheet consists of one column containing income, one column containing all expenditure and eleven columns splitting up the different categories of expenditure. At the moment, if I need to work with only one particular category, I do a customised sort, selecting the relevant column. Unfortunately this doesn't work very well. Is there a way in which one can create eleven separate sheets, containing the eleven categories of expenditure that are automatically updated as one populate the eleven columns?

Nico Smith

ANSWER: It would be possible to use VBA to do something like this, but I'm always reluctant to suggest solutions that effectively involve duplicating data - I would suggest using a final column which concatenated the expense columns - something like


This would then give you a column you could auto-filter on, using the contains custom filter choice.

---------- FOLLOW-UP ----------

QUESTION: I'm not quite sure about this Aidan. Can you elaborate? My spread sheet currently has: Column A = all income; column B = all expenditure, then in columns C to M = the different categories already captured under column B (but this data is not very readable because it is chronologically arranged according to the entries in column B with lots of empty cells in between. (I basically need these entries to be automatically sorted with the empty cells eliminated without having to do a customised sort every time). So if I follow your suggestion, do I not end up with a column similar to my current column B?

Or is the best solution not to create eleven macros and do the sort using keyboard shortcuts? The problem with this is just the length of the entries in the columns that expand constantly as the sheet is populated.


As I say, it would be possible to create extra sheets, but I'm always worried about solutions that duplicate data - my problem is one of understanding the requirements here - it might help if I could see a sample file if that is possible?  My email is
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Aidan Heritage


I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!


My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 All rights reserved.