Excel Tips for Accounting: Grouping Rows and Columns

The collapse button on reports in QuickBooks is great. Pull up a P&L with sub-line items and with one click they all collapse. When you export the P&L into Excel however that collapse button does not export with your data. Not to worry, there is a neat workaround in Excel.

To group a series of rows, say for example different types of income, highlight the rows you want to collapse. In the image below as an example, if I wanted to hide the different states, I would highlight rows 3-5. In the Data tab in the ribbon click Group. Magically the rows will disappear and a box with a plus sign in it will appear next to space where the rows used to be. To view, the rows click the plus sign and the rows will reappear. A box with a minus sign will then show which can be clicked to collapse the rows.

At the top left of the workbook, there will be a number in a box as well. Clicking the lowest of the numbers will show the least data while clicking the largest number will show all rows. These buttons affect all rows. So clicking number 2 would hide rows 3-5, 8-10, and 13-15. Clicking number 1 will collapse everything and just show Total Income. Clicking 3 will expand all rows.

Do you have an Excel question? Are your reports saving you time or taking up time? Let us help you streamline your reporting and teach you some time-saving Excel practices. Drop us a line.