When analyzing large amounts of data in Excel, often the best approach is to retrieve the top (10) values. Consider the following example: This table contains all of the invoices from January 29th. There are 904 invoices for that day. The values in the invoice column are unique, as those are invoice numbers. The values in the user column are not unique, as some users have multiple invoices on that day, and the values in the team column are not …
Category: Excel Tips & Tricks
How to customize the Quick Access Toolbar
In Microsoft Excel, the Quick Access Toolbar is a customizable toolbar independent of the tabs on the ribbon. It is usually located in the title bar of the Excel window, but it can also be configured to display below the ribbon. It will look something like this on a new installation: You can see the often-used commands Save, Undo, and Redo already on the toolbar. If you want to remove those, or more likely, add some commands you often …
Remove Duplicates feature
You will sometimes want to retrieve unique values from a data set, i.e., to find and remove duplicate values. While there are a few ways to do this, the most effective option is the Excel built-in feature Remove Duplicates. The Remove Duplicates feature removes duplicate rows from the selected columns. Duplicate rows are deleted in this process. The simplest example of this process is the removal of duplicate values from a single column: In this example, we’ve started with …
Custom Icons for Conditional Formatting
With conditional formatting, among other things, we can join indication icons to our numbers. While we can rearrange available icons as we wish, the choice of available icons is fairly limited: In particular, while Green Arrow Up and Red Arrow Down are available, we don’t have the often-requested Green Arrow Down and Red Arrow Up. We can expand our choice of icons with the help of the Wingdings fonts, a series of fonts that render characters as a variety …