Microsoft Excel is a very powerful tool – a simple workbook with many possibilities. It’s used in businesses, schools and is one of the original tools for entering and analyzing data. As a small business, it might seem like a good idea to track all your data in Excel because it’s simple to use and cost-effective. But, it is not the best tool to use for sales leads or even tracking inventory because the more your business grows, the more likely that data is prone to errors. Entering 50 lines of inventory items per day may not seem so bad and will even give you enough time to double-check all the entries, but can you imagine having to manually enter 500 lines of inventory? You definitely wouldn’t have time to double-check each one and the idea of 0 errors, is highly unlikely. But, don’t completely get rid of Excel. Instead, use it in conjunction with an ERP software such as Blue Link ERP and you’ll be amazed at how you can automate your data analysis processes and use the below advanced Excel functions that are going to assist with your analysis.
Below we will look at 7 formulas, 4 of which are part of the spill formulae which means that if you enter anything into a cell that needs to be used from one of those formulas, you will end up with a #SPILL error. Essentially, these formulas are entered into one cell and spill into the other cells to populate them – as opposed to each cell containing a separate formula. Spill formulae contain the Sort, SortBy, Filter, and Unique formulas. It is important to remember that these formulas in the example are being used with lived linked data from Blue Link ERP allowing you to look at all your original source data in one tab and narrow it down to reflect a slice of it in another tab for easier analysis.
Advanced Excel Functions:
- Sort and SortBy
- Filter
- Unique
- Xlookup
- Aggregate
- 3D totals
- Flash Fill
Sort and Sortby
As a basic excel skill, you may be familiar with the option of sorting data by clicking on the right sort button at the top of the chart and sorting by that column. This will sort the whole data set whether it be alphabetically or descending values – whatever you decide. However, using this technique affects your live linked data which is not always the best when you want to analyze different aspects of it.
The Sort formula allows you to take a set of data and sort it using multiple components. For example, you can sort a whole table specifically using one column ‘amount’ and then also sort it by ‘descending amount’. This will help you to visually see your data in descending amounts as opposed to limiting the sort by only one circumstance. Doing it this way does not affect the live linked data from your original data set because it is a new formula on a different tab and, whenever you refresh or update your original data set, your Sort formula will automatically update as well.
You can also use the Sortby function which allows you to sort your data using a column in the original source data that you do not want to show in the result set. For example, let’s say you want to get a list of your sales and see products and quantities that are sorted by vendor but you don’t want to see the vendor in your result set, then the Sortby function is your new best friend.
Find the tutorial at 6:20 in the video above.
Filter
This function filters your original source data and only returns some of the rows. This is used as opposed to filtering right in the original data set because all it will do is hide rows which can get messy if you don’t realize some information is not shown. The filter function is great for accounts payable analysis and when you only want to see certain values but consider others. For example, in the photo below we’ve used the cell F7 as a point of reference for Excel to look at when sorting the data. If that number is 100, the data will only show numbers equal to or greater than 100 and if changed to 2,500 then we only see data that amounts to equal to or greater than 2,500. Just like the Sort and Sortby functions, this formula keeps your original source data clean and gives you the ability to analyze a slice of it.
TIP: If you wrap the filter function with the sort function, you can actually sort that resulting data by additional criteria. For example, you can sort by items over 2500 with a descending date.
Find the tutorial at 16:46 in the video above.
Unique
The unique Excel function allows you to return only unique values of a set of data that has multiple copies of a value. For example, use this formula when you want to see which specific products you’ve sold over the last three months. It is likely that when you try to see this manually, there is just too much data. If you enter =UNIQUE and highlight the data you want to see, it will only return one instance of every product code no matter how many times it appears in the original data set. Watch the video below to see further examples of how you can use this function.
Find the tutorial at 25:30 in the video above.
Xlookup
The Xlookup formula allows you to look up an item in one column and return the data in another column. You tell it what you want to look up, where to look it up based on a column, then tell it which column contains the results. For example, let’s say you sell home equipment including electronics, furniture, and paint. You can use the Xlookup formula to return ‘electronics’ every time you type in ‘TV’ as that is the corresponding department. This formula is a great replacement for VLookup which can be finicky to deal with as you have to meet certain criteria when using the formula.
Find the tutorial at 34:12 in the video above.
Aggregate
If you’ve ever worked in a data set where you want to sum, multiple or divide values but some of the values are text values as opposed to numeric values, then you’re familiar with the #VALUE! error. The aggregate function allows you to sum your data and ignore error rows – treating them as if they don’t exist.
Find the tutorial at 43:08 in the video above.
3D Totals
Let’s say you have sets of values in three different tabs. All values are in the same column (column D) and you want to sum all the values in each tab for column D. In this scenario, you can use the 3D total function. The 3D total function will allow you to select a range in one tab and by holding down the shift key and highlighting the other tabs it is theoretically highlighting all values in column D for all tabs. This will sum those values for you much quicker than manually summing each tab.
Find the tutorial at 49:05 in the video above.
Flash Fill
The flash fill function is as simple as it sounds. It will fill your data when it senses a pattern.
Find the tutorial at 50:41 in the video above.
We at Blue Link Associates use these advanced Excel functions for our own books and accounting at year-end as well as working alongside Blue Link customers to analyze and make sense of their data.
To learn how to live link data from Blue Link, watch this video.
