I have been an accounting software purist for much of my career, holding the stance that all accounting should happen within the software. That with good, well-implemented software, that is possible. And enhanced excel processes like v-lookups and filters and pivot tables were work-arounds that shouldn’t be needed if the system was maintained well through organizational changes etc..
While I still like thinking those thoughts, I’ve come around to the fact that there *are* valid uses of those functions in accounting and accounting-adjacent work.
For instance, pivot tables. Pivot tables used to really bother me, I didn’t really get how they worked, I didn’t like how the field list kind of overlaid the resulting table, and it was there and also not there, etc.. Seemed like a lot of flim-flam.
Now I’m a convert. Pivot tables are great! So in case of any of my readers are pivot-table positive but haven’t actually dived in very much, I thought I’d share a run through. Part of me thinks – there’s no reason to do this, everyone else already knows this.. but in my work experience I have come across people who don’t use Excel with any comfort level at all. So offering this in case of use.
Here’s my scenario – not strictly accounting (because of my belief system still), but set in a usual nonprofit scenario. This nonprofit has lots of volunteers that pitch in to help with the work of the nonprofit. This work takes place in 5 different ways (nature-based activities, food-security activities, tax preparation, etc..) and at 10 different sites. The leadership team would like a simple view of how many volunteers pitched in during a year.
The starting point is your spreadsheet with columns for the dates of the events, the type of work that volunteers are helping with (blood drives for different populations, services for high schoolers including theater/art as well as FAFSA support etc..), the 5 locations in use during the year, and the number of volunteers at each specific event. If your spreadsheet has additional info that isn’t necessary (like the staff member overseeing the event, the grant number for that event, the number of waiver forms collected etc..) that’s fine – it won’t mess anything up.
You select all the data in your spreadsheet, click on Insert in the top menu, and then the left-most option – pivot tables. A little box will pop up confirming the area of the data, and that you want the pivot table in a separate sheet (is usually done that way), can say ok.
Then you’re in your new sheet, there is a gray box on the left, and your field list on your right.
Select the fields you want to include (in our case: type of work, location, qty of volunteers).. it will put them in boxes as your selecting, you can finish your selections as it’s doing that.
Then, review what’s in which box. The lower box on the left is row – I’m thinking that would be your ‘type of work’ field, or it could be your ‘location’ field. Or you could do it both ways and see what you like better. The top box on the right is columns – whichever other field you didn’t use. Then on the lower right is quantity – in this case your number of fields.
And voila – you have a handy chart summarizing the year’s activities.
For more complicated cases, there are some settings that I was trained it to use, and it’s worked well. For instance, if you want to include both the site name and the city on each row, and city is in your spreadsheet, also check that box, and put that field also in your row box.
The I suggest you right-click on the resulting table first of all, click on ‘PivotTable Options’ (second from bottom), go to the Display tab in the box that opens up, and select ‘Classic PivotTable layout’ which enables dragging of fields in the grid. Also the resulting format is sort of flatter or something, I like it better.
Then in your rows, it’s going to think that each field should be subtotaled, which I don’t want. To get rid of that if you also don’t want that – click on one of the subtotals, and uncheck ‘Subtotal field name’ almost half way down that popup menu, and those go away throughout the grid.
Now hopefully your grid is looking pretty good, but maybe unwieldly still. It sets the columns to the length of the field names – you can narrow that yourself. You can also freeze the view if your list is longer than our example. Filtering and sorting are pretty workable.
Also if your underlying data changes, you can generally update the pivot table – but it’s not the most perfect event ever. But the way you do it is to click on the pivot table, and then you’ll see two new choices in your top menu bar, in green (in my versions anyway). The first says ‘PivotTable Analyze’ and the second one says ‘Design’. Design is really fun- you can do gradient colors and all sorts of things. But instead click on ‘PivotTable Analyze’, and just under and to the left slightly you’ll see ‘Refresh’ with a down arrow. Clicking on that lets you refresh just the part of the table you have highlighted, or ‘Refresh all’. Choosing ‘Refresh all’ will re-generate the whole table. Your columns will resume their field-name length, and if the dataset contains more of the field you selected for your column, then your table will get wider and will overwrite anything you have entered on the side of the table (it warns you if that’s going to happen). You can stop, then move over anything necessary and go ahead after that.
If you have for instance a report exported from a system, and you’re not quite sure how best to make sense of it – the pivot table can be a good way to explore it and see what different combinations look like. Both your rows and your columns can contain multiple fields.
The choices are yours!
Clarity Solutions 