Microsoft Excel. Do those two words make you tremble? Do they remind you of hours wasted sifting via thousands and thousands of rows of information? Properly, if they do, they should not. You can use a handful of straightforward tricks to analyze data quicker than ever just before.

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

When tracking your enterprises overall performance, it is helpful to build subtotals of sales, of stock, by division, by date…the list is pretty much endless. Basically, you want a reporting dashboard whereby you can select any element of your firm and view its existing overall performance.

You are almost certainly conscious that you can auto-filter tables in Microsoft Excel. This indicates that your table with 20 columns and 1000 rows can be sorted and filtered by any column e.g. date. That way, you can swiftly view e.g. all your orders for March. So far, this ought to sound familiar. Wouldn’t it be good if the act of filtering your table also updated your dashboard?

The fantastic news is that they can, and that you do not need to have to be an Excel professional to attain this. Let’s say you have a list of amounts in Column B. You may well have calculated the total using the formula “=SUM(B:B)”. When you filter by date, the total amount does not alter. This is due to the fact the other orders nevertheless exist, you just can not see them at the present time.

What you want is an option to the SUM function that only counts the visible rows. Thankfully, one exists, and it is the SUBTOTAL function. The SUBTOTAL function can sum data, it can typical data, it can count data, it can do fairly a lot anything to data. The difference in between the SUBTOTAL function and any other Excel function is that it only includes the displayed information in its calculations.

The SUBTOTAL function will offer subtotals for the data displayed in filtered tables. It can aid you make uncomplicated, flexible, numeric reporting dashboards. Unfortunately it is not substantially superior if you wish to plot your data on charts. If you create a bar chart to track month-to-month overall performance, it is not considerably excellent if you are totalling January and February’s data in specifically the very same cell. advanced excel course is consequently also helpful if you can subtotal every month’s data simultaneously.

This can be done working with the SUMIF and COUNTIF functions. The SUMIF function lets you SUM all the information linked with a certain value e.g. all the sales in March. The COUNTIF function lets you COUNT how many items of information are related with a particular value e.g. how many orders had been received in April.

You may possibly assume these two functions are a bit limiting as the COUNTIF function will not let you count how quite a few orders of more than $500 have been received in April e.g. you can only count based on a single criteria. This is in contrast to our filtered table exactly where it is completely feasible to show only orders of more than $500 that have been received in April.