Sometimes Excel surprises me. In this case it sorts in a way I didn’t expect, but in a good way. Thanks to Mr Excel for the tip.
See the image below. It is a list where we have used the Automated Subtotal feature from the Data ribbon.
If you are unfamiliar with the Automated Subtotal check out this post.
If we click the small number 2 in the top left, the display changes to the image below. This shows just the subtotals and the grand total.
This is where things get interesting.
If you want to sort the amount column from largest to smallest for the subtotals amounts you can select the Amount cell C1 and then click the Sort Descending icon in the Data ribbon tab – see below.
Magically the table changes to put the highest subtotal and all the underlying data at the top – as per the image below.
If we expand out the selection (see image below) you can see that while the subtotals have been sorted the individual values have not been sorted.
If you need the individual amounts sorted too, you would have to apply that sort order before you applied the Automated Subtotals.
Inquire is a new add-in in some versions of Excel 2013 and later versions It is an auditing Add-in that can analyse and report on your Excel files.
Some Accounting systems (I think SAP is one) downloads negative values with a trailing minus sign. Excel doesn’t recognise this as a number. When you import TXT files, negatives are handled correctly. CSV files don’t.
I use Alt key shortcuts a lot when I am working and I have found a couple more useful ones.
Let’s say you have a table of codes and every month there are a few you want to check out. You could use a VLOOKUP to extract all the details for each code, but let’s say you want to view the codes in the table.
I wanted to offer a solution to a common problem I see in Excel. It relates to creating totals in data that isn’t structured that well.
It is common to work with lists in Excel. Lists of departments, names and other categories you frequently use. This blog post covers a few techniques that work really well together to create robust reporting systems.
I saw a technique demonstrated recently with VLOOKUP that I hadn’t seen used before and thought at the time, that’s handy. Upon reflection however, I thought that’s a bit dangerous.
Well after getting the data and creating a report and then a chart, let’s get the report onto the web.
Yes, you can sort by colour in Excel! This feature makes it easy for you to colour code cells and then place them together at the top of your data set.
Dashboard Charts are the ultimate goal of most Power BI reports, so let’s dive in.
The Excel team has a great site that encourages people to post new ideas for Excel. It also encourages people to vote for the new suggested features.
I have just posted an idea for a UNIQUE function that extracts unique entries from a list.
Currently you have to manually maintain a separate list of entries for a drop down lists. You can’t use a list that contains duplicates for a drop down list.
What if that list was updated automatically via a formula from the original data source? So as new items are added at the data source they automatically appear in the drop down list?
Currently a dynamic solution requires a complex array formula or a UDF (User Defined Function – macro).
This would also make creating formula-based reports so much easier.
Please take the time to vote for my suggestion. Feel free to post your own suggestions as well.
Vote For UNIQUE Function
In the previous post we extracted the data from a CSV file. Now let’s create a report. It won’t be the greatest report as the data is pretty basic, but at least its a start.
It is common to use Q1 for quarter one. Excel will even cycle through Q1,Q2,Q3 and Q4 when you drag a cell contain Q1. What if you want to use the sequence M1 to M12 for months? Custom Lists to the rescue!
OK I have bitten the bullet and decided to get stuck in to Power BI. I am going to start playing around with Power BI Desktop – it is free after all. I thought I would blog about the experience and share my journey. I have created a Power BI category. I now have a button on my website that will list Power BI posts.