Microsoft Excel. Do those two words make you tremble? Do they remind you of hours wasted sifting by way of thousands and thousands of rows of data? Properly, if they do, they should not. You can use a handful of very simple tricks to analyze data more quickly than ever before.

I’m going to assume you are an intermediate Excel user, and are comfortable with fundamental Excel formulas, such as the SUM function. You may perhaps have heard of pivot tables, but are not confident with generating them your self. In other words, you use Excel to develop tables with a view to building basic reports.

When tracking your businesses overall performance, it is beneficial to generate subtotals of sales, of stock, by division, by date…the list is nearly endless. Basically, you want a reporting dashboard whereby you can choose any element of your business and view its present efficiency.

You are likely aware that you can auto-filter tables in Microsoft Excel. This implies that your table with 20 columns and 1000 rows can be sorted and filtered by any column e.g. date. That way, you can speedily view e.g. all your orders for March. So far, excel course Dubai must sound familiar. Would not it be wonderful if the act of filtering your table also updated your dashboard?

The great news is that they can, and that you don’t require to be an Excel expert to accomplish this. Let’s say you have a list of amounts in Column B. You may perhaps have calculated the total utilizing the formula “=SUM(B:B)”. When you filter by date, the total quantity does not change. This is for the reason that the other orders still exist, you just cannot see them at the present time.

What you want is an alternative to the SUM function that only counts the visible rows. Luckily, 1 exists, and it is the SUBTOTAL function. The SUBTOTAL function can sum data, it can typical information, it can count information, it can do pretty a lot anything to data. The distinction among the SUBTOTAL function and any other Excel function is that it only contains the displayed information in its calculations.

The SUBTOTAL function will provide subtotals for the data displayed in filtered tables. It can aid you create straightforward, flexible, numeric reporting dashboards. However it is not a great deal 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 a great deal excellent if you are totalling January and February’s data in exactly the similar cell. It is for that reason also useful if you can subtotal every month’s information simultaneously.

This can be performed utilizing the SUMIF and COUNTIF functions. The SUMIF function lets you SUM all the data related with a specific worth e.g. all the sales in March. The COUNTIF function lets you COUNT how lots of items of information are linked with a particular worth e.g. how numerous orders had been received in April.

You might think these two functions are a bit limiting as the COUNTIF function will not let you count how a lot of orders of over $500 had been received in April e.g. you can only count primarily based on one criteria. This is in contrast to our filtered table where it is perfectly doable to show only orders of more than $500 that were received in April.