30 Aug How to Track Excel Template Submission Versions
Howdee! A big part of some analyst’s job is usually collecting data via templates and aggregating that data into reports for leaders to review. This presents a problem. Many times, the way users want to enter data, is not favorable for aggregating reports or inserting data into a database. This can lead to complex formulas and lots potential issues. Let’s look at a revenue forecast document for example. Typically, you’ll send a document where each column represents a month of the fiscal year. However, month really should be a single field from a data perspective. Turning Excel columns to rows of data is a common requirement when aggregating data. Fortunately, there exists an effortless way of accomplishing this, and it has the added benefit of allowing you to version your data submissions!
Prep the Data
As with most things in Excel, getting the data ready is most of the battle. In this case, you need to closely examine what column’s you’re wanting moved to row data and what other data needs to be included to make sense of it. I’ve made this example data below to demo this:
In this simple example, we have a monthly forecast, the revenue streams the forecast represents, and who submitted it. The reason to decide beforehand, is that we need to consolidate all the columns we want in our final data set into one “key” column. This may sound strange, but it will make sense shortly. Beyond combining the columns, we need to delimit them so we can separate them for later use. I typically concatenate them with the pipe (shift + backslash key to get “|”) delimiter. However, if any of your columns contain pipes, you’ll need to reconsider which key to delimit with.
Next, we need to make sure that there are no blank cells in the columns we want to move to row data. If the cell is blank, that result will be missing from our final data. If that won’t affect you, then you may not need to worry about this, but I’ve found it to have adverse effects depending on what I am doing with that final data. Lastly, we need to make sure all the columns we want to perform the columns to rows action on are grouped together and that our “key” column is inserted directly beside it. The result is a column of data that looks like this:
A side note here, since some of you might be questioning the “CONCAT” formula vs. “CONCATENATE”. That came with a recent Microsoft update for Office 365 users a few months ago. The only difference is that CONCAT accepts range inputs where CONCATENATE is limited to one cell per input. This is helpful when you need to concatenate several fields together to make a unique ID for a row.
Excel Columns to Rows
Moving on, the next step is to use a little-known shortcut to access the old Office menu. I don’t think that many people are aware that, pressing “Alt + D” allows you to access some of the older functionality of Excel. In this case, the full shortcut is “Atl + D + P”. Some of you may remember this old user interface for creating pivot tables through the pivot table wizard. There are a lot more options that affect how the data is stored inside of the cube. For us we want to first select the “Multiple consolidation ranges” radio button, and keep the “PivotTable” radio button selected in the second option and click next. In the second window, select the “I will create the page fields” option and click next. Finally, click the up arrow next to the Range box, and select your range. It should only include the “key” column, and the columns we want to move to rows.
Once you select finish, you’ll be directed to a new worksheet where you’re pivot table will be populated. It will look just like the data you selected to be pivoted except it will have totals for columns and rows. This means everything worked great! Also, if you did have blank cells in the data you select to be pivoted, you’ll most likely end up with a PivotTable that reflects counts instead of sums. It’s not a big deal if that data isn’t important to you but, if it is, you’ll need to back up a few steps and get rid of those blank cells. A find and replace typically works for me.
Next, double click on the cell of the pivot table where the grand total for rows intersects the grand total for columns. See picture below for example:
The result of this will be a data table with three columns. The first will be the concatenated “key” column. The second will be the “columns to rows” columns. Finally, the third will be the values from those columns. The last step is to expand our concatenated column back out so we have all the data we want back in one spot. Immediately to the right of the first column, I will insert two columns (one for the extra field and I concatenated and one for the version). Then use the “Text To Columns” functionality to un-concatenate your string as pictured below. You will get an error telling you there is text already here but don’t worry, that’s just because of the table column headers. Finally, rename your columns and you have a brand new data set where all of your values are in one column and you know what submission version this came from!
Summary
Discovering this method literally changed everything about the way I handled reporting. I could easily compare submissions side by side and see what changed from the previous submission. Before, I had to use incredibly complex formulas linked across multiple files to do this. Now I just keep adding to a data table, pivot the results, and I can compare any submission I’ve previously stored using this approach. Hopefully you find this excel columns to rows example as helpful as I have over the years.
As usual, I’ve saved this file in the “Example Files” section of my site. If you’re a subscriber, you can download it for free. It just takes a minute to sign up. It’s free and I won’t spam you with tons of unwanted emails. If you have questions on anything in this article, drop them in the comments below!
Cheers,
R
No Comments