The original file was sent to me by a friend to see if I could work out how it was done. I did, and so I posted the how to. You can find the original here: http://www.myonlinetraininghub.com/excel-3d-financial-reports. Great work!!!
Excel has some great functions, the first being it’s ability to collate data in tables. However in general it’s difficult to see more than two parameters in a standard table. For example you might want to show k$ sales by business unit and by area:
This is great, but what if you also wanted to show this data by year as well. Normally you would need several tables for this each showing a different year. Not easy or quick to understand and interpret the data.
One way around this is to use the camera tool to make a 3D table:
How is this done? If you’ve never used the camera tool, a previous post of mine “How to format data using the camera tool” might come in handy! But first, you need to create your tables. You can download the sample data sheet here.
Step 1 Format the data
Lets imagine you have three tables for the years 2012, 2013 and 2014:
These are going to form the left, top and right of the 3D table going from top to bottom. In the first blank cell of the first table (Asia, SBU A), you need to calculate the sum of all of the k$ sales in Asia for SBU A. If your data is set up like the example raw data shown, and cell A3 is “2012”, then your calculation might look something like this:
For Asia and SBU B it would be
You can of course copy and paste the formula across the rows and down the columns of the table. For the next table (By year, by region), again you need to calculate the sum of the k$ sales in each region for each year. The first calculation in cell H13 (Asia, 2012) might be:
This time you can copy and paste the formula across the row, but it won’t work down the columns, instead you’ll need to calculate 2013 and 2014 separately. 2013 for Asia might look like this:
Finally in the last table, you need to calculate the k$ sales by year and by SBU. Your first calculation (2014, SBU A) might look like this:
Note that the last year comes first in this table. This is important for the layout of the final 3D table. Fill the entire table as before and you are ready for the next step.
Step 2: Use the camera tool
Again, if you are not familiar with this tool, see this previous blog: “How to format data using the camera tool“. Otherwise, highlight the first table including the side headers, but not the top headers and click on the camera tool button (if you cannot see the tool on the ribbon, you need to customize your ribbon as it is not normally visible – the previous post explains how to do this in full).
Once you have the new image, repeat for the second table, but this time include all of the headers. Repeat again for the third table, but this time don’t include any headers at all. You should then have three new image tables. Note: these images will update when you update the original tables!
Step 3: Rotate the images to create the 3D table
Click on the first image and press Ctrl+1. A pop up menu as shown below should appear:
Click on the 3-D Rotation tab and choose the preset “Isometric left down” from the parallel category.
Click on the second image and repeat, choosing the preset “Isometric bottom down”. The third image should use the preset “Isometric right up”. You can then press the close button. Occasionally, you might need to resize one of the camera images once you’ve repositioned them to make the table fit perfectly. Don’t change the size while it is rotated. Remove the rotation first and then resize, or you will alter the angle as well.
Once it’s done your table should look like this:
You can add formatting (as I have done) to highlight specific SBUs or years or regions as you wish. Again, you can download the sample spreadsheet here. Hope it’s useful.