Filtering And Analyzing Data In Excel

By Author: Ed Bolton
Total Articles: 17
Comment this article

Microsoft Excel. Do those two words make you tremble? Do they remind you of hours wasted sifting through thousands and thousands of rows of data? Well, if they do, they shouldn't. You can use a few simple tricks to analyze data faster than ever before.



I'm going to assume you are an intermediate Excel user, and are comfortable with basic Excel formulas, such as the SUM function. You may have heard of pivot tables, but are not confident with creating them yourself. In other words, you use Excel to create tables with a view to creating basic reports.



When tracking your businesses performance, it is useful to create subtotals of sales, of stock, by division, by date...the list is almost endless. Essentially, you want a reporting dashboard whereby you can select any element of your company and view its current performance.



You are probably aware that you can auto-filter tables in Microsoft Excel. This means that your table with 20 columns and 1000 rows can be sorted and filtered by any column e.g. date. That way, you can quickly view e.g. all your orders ...
... for March. So far, this should sound familiar. Wouldn't it be great if the act of filtering your table also updated your dashboard?



The good news is that they can, and that you don't need to be an Excel expert to achieve this. Let's say you have a list of amounts in Column B. You may have calculated the total using the fomula =SUM(B:B). When you filter by date, the total amount does not change. This is because the other orders still exist, you just can't see them at the present time.



What you want is an alternative to the SUM function that only counts the visible rows. Fortunately, one exists, and it is the SUBTOTAL function. The SUBTOTAL function can sum data, it can average data, it can count data, it can do pretty much anything to data. The difference between the SUBTOTAL function and any other Excel function is that it only includes the displayed data in its calculations.



The SUBTOTAL function will provide subtotals for the data displayed in filtered tables. It can help you produce simple, flexible, numeric reporting dashboards. Unfortunately it is not much good if you wish to plot your data on charts. If you build a bar chart to track monthly performance, it is not much good if you are totalling January and February's data in exactly the same cell. It is therefore also useful if you can subtotal every month's data simultaneously.



This can be done using the SUMIF and COUNTIF functions. The SUMIF function lets you SUM all the data associated with a certain value e.g. all the sales in March. The COUNTIF function lets you COUNT how many items of data are associated with a certain value e.g. how many orders were received in April.



You may think these two functions are a bit limiting as the COUNTIF function will not let you count how many orders of over $500 were received in April e.g. you can only count based on one criteria. This is unlike our filtered table where it is perfectly possible to show only orders of over $500 that were received in April.



The solution is always to break your analysis into different stages. You can add an extra column to your table and use Excel's IF function to populate it with 1s and 0s based on whether an order's value exceeds $500. You can then use the SUMIF/COUNTIF functions on the new column of data. To keep things tidy, you can then hide the column.



About the Author: Ed Bolton is the founder of Excel4Business, and an Excel expert.