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? Well, if they do, they shouldn’t. You can use a few straightforward tricks to analyze information more quickly than ever ahead of.

I am going to assume you are an intermediate Excel user, and are comfy with simple Excel formulas, such as the SUM function. You may perhaps have heard of pivot tables, but are not confident with making them oneself. In other words, you use Excel to develop tables with a view to creating basic reports.

When tracking your companies overall performance, it is useful to produce subtotals of sales, of stock, by division, by date…the list is just about endless. Primarily, you want a reporting dashboard whereby you can choose any element of your corporation and view its existing efficiency.

You are in all probability 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. Excel Course in Gurgaon , you can immediately view e.g. all your orders for March. So far, this should really sound familiar. Would not it be terrific if the act of filtering your table also updated your dashboard?

The great news is that they can, and that you never need to have to be an Excel expert to accomplish this. Let’s say you have a list of amounts in Column B. You could have calculated the total utilizing the formula “=SUM(B:B)”. When you filter by date, the total quantity does not alter. This is because the other orders nonetheless 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, one particular exists, and it is the SUBTOTAL function. The SUBTOTAL function can sum information, it can average data, it can count information, it can do fairly considerably anything to information. The distinction among the SUBTOTAL function and any other Excel function is that it only consists of the displayed information in its calculations.

The SUBTOTAL function will deliver subtotals for the data displayed in filtered tables. It can support you generate very simple, versatile, numeric reporting dashboards. Sadly it is not substantially fantastic if you want to plot your data on charts. If you construct a bar chart to track month-to-month overall performance, it is not a lot great if you are totalling January and February’s information in precisely the same cell. It is thus also helpful if you can subtotal every single month’s data simultaneously.

This can be completed using the SUMIF and COUNTIF functions. The SUMIF function lets you SUM all the information linked with a specific value e.g. all the sales in March. The COUNTIF function lets you COUNT how quite a few products of data are related with a certain worth e.g. how several orders had been received in April.

You may perhaps feel these two functions are a bit limiting as the COUNTIF function will not let you count how numerous orders of over $500 have been received in April e.g. you can only count based on one particular criteria. This is as opposed to our filtered table where it is completely probable to show only orders of over $500 that have been received in April.