All articles with 'data filters' Tag

Summarize only filtered values using SUBTOTAL & AGGREGATE formulas

Published on Aug 25, 2015 in Learn Excel
Summarize only filtered values using SUBTOTAL & AGGREGATE formulas

We all know the good old SUM() formula. It can sum up values in a range. But what if you want to sum up only filtered values in a range? SUM() doesn’t care if a value is filtered or not. It just sums up the numbers. But there are other formulas that can pay attention […]

Continue »

Filter as you type [Quick VBA tutorial]

Published on Aug 22, 2015 in VBA Macros
Filter as you type [Quick VBA tutorial]

Filtering a list is a powerful & easy way to analyze data. But filtering requires a lot of clicks & typing. Wouldn’t it be cool if Excel can filter as you type, something like above.

Let’s figure out how to do this using some really simple VBA code.

Continue »

Quickly filter a table by combination of selected cell values using VBA

Published on Jul 15, 2015 in VBA Macros
Quickly filter a table by combination of selected cell values using VBA

Filtering is one of the most used feature in Excel. It is a quick way to take lots of data and narrow down to the subset we want.

But here is one common filtering scenario that is slow as snail.

Imagine you are looking at some sort of sales data (if you can’t imagine, look at the above demo).

Now, you want to filter this list for gender=male, profession=self-employed, product category = chocolates and  quantity = 1.

If you use the right click, filter > filter by selected value approach, this will take several clicks.

Wouldn’t it be cool if you can select the entire combination and say filter?

Unfortunately, no such feature exists in Excel.

But you are not aiming to be ordinary in Excel.  You are aiming to be awesome in Excel. That means, you don’t take no for answer.

Fortunately, we can quickly write a VBA macro that filters a list by selection. So let’s do that.

Continue »

Learn Top 10 Excel Features

Published on Apr 16, 2013 in Charts and Graphs, Learn Excel, Power Pivot, VBA Macros
Learn Top 10 Excel Features

Last week, we had a lovely poll on what are your favorite features of Excel? More than 120 people responded to it with various answers. So I did what any data analyst worth his salt would do,

I analyzed the data and here are the top 10 features in Excel according to you.

Read on to learn more.

Continue »

Excel Links – After a long time edition

Published on Oct 24, 2011 in excel links

Long time readers of Chandoo.org know that we have a regular featured called as “Excel Links“, where I share some interesting Excel articles published around the web. This used to be a weekly feature, then became a monthly thing and later once a few months thing. As I had a hectic weekend, when I got […]

Continue »

Filter values where Fruit=Banana OR Sales>70. In Other Words, How to use Advanced Filters?

Published on Oct 10, 2011 in Excel Howtos, Learn Excel
Filter values where Fruit=Banana OR Sales>70. In Other Words, How to use Advanced Filters?

Filtering is a simple yet powerful way to analyze data. When you apply filters to any list of values, Excel gives you some really useful pre-defined filters so that you can analyze the data in a variety of ways. So, assuming you have data like this: We all know how to filter data for Bananas. […]

Continue »

10 Excel Keyboard Shortcuts I can’t live without!

Published on Aug 8, 2011 in Learn Excel
10 Excel Keyboard Shortcuts I can’t live without!

Everyone and their colleague knows the importance of Excel keyboard shortcuts. While it may be a good idea to know a 97 different shortcuts for everything in Excel, the fact is, you don’t use them all the time. So, it all boils down to memorizing the few shortcuts that you use all the time.

In this post, I am sharing 10 such shortcuts that I can’t live without, especially, when I am analyzing data, making charts and formatting my workbooks.

Continue »

What are Pivot Table Report Filters and How to use them?

Published on Apr 20, 2011 in Pivot Tables & Charts
What are Pivot Table Report Filters and How to use them?

Today we will learn about Pivot Table Report Filters.

We all know that Pivot Tables help us analyze and report massive amount of data in little time. Excel has several useful pivot table features to help us make all sorts of reports and charts.

Report Filters are one such thing.

Continue »

How to Filter Odd or Even Rows only? [Quick Tips]

Published on Jan 5, 2011 in Excel Howtos
How to Filter Odd or Even Rows only? [Quick Tips]

Ashish sends out this SOS thru email, “I need your help in putting filters . Can we filter the cells on the basis of their even or odd character. i.e in the table of 1-1000, i wish to filter, 1,3,5,7,…”

An odd request, I must say. But nevertheless, possible in Excel.

Read this quick tip, If you want know how to filter odd or even rows only in a list of values.

Continue »

Use Filter By Selected Cell’s Value to save time [Quick Tips]

Published on Dec 22, 2010 in Learn Excel
Use Filter By Selected Cell’s Value to save time [Quick Tips]

We are busy decorating the Christmas tree, making preparations for the holidays. But I have a very quick tip for you.

[Note: all these tips work in Excel 2007 or above]

Whenever you are working with huge lists of data, filtering & sorting is one simple way to analyze the data quickly.

You can quickly filter your data based on current cell’s value by right clicking and then selecting filter > filter by selected cell’s value.

Continue »

Make Dynamic Dashboards using Pivot Tables & Slicers [Video & Download]

Published on Dec 8, 2010 in Charts and Graphs, Pivot Tables & Charts
Make Dynamic Dashboards using Pivot Tables & Slicers [Video & Download]

Do you know that Excel 2010 makes creation of dynamic dashboards very simple?

Yes, that is right. Using slicers feature, you can create dynamic excel dashboards from your data in very little time. Today we are going to learn a technique that will help you create a dashboard like below.

Read rest of this post to find out how to construct a dynamic dashboard in Excel & download the example workbook.

Continue »

How to Check whether a Table is Filtered or not using Formulas

Published on Mar 29, 2010 in Excel Howtos, Learn Excel
How to Check whether a Table is Filtered or not using Formulas

Let us start the week with a simple formula (well, to be fair, let us start the week with a strong cup of coffee, then this formula).

Often when we have large data sets, we apply data filters to select and display only information we want to see.

Some of you know that whenever we apply filters on a dataset, we can look at status bar area to find out if any filter is applied on the current worksheet.

But, what if you need a way to show “filtering” status thru formulas? Like this…,

Continue »

Exploring Profit & Loss Reports [Part 4 of 6]

Published on Mar 3, 2010 in Learn Excel, Pivot Tables & Charts
Exploring Profit & Loss Reports [Part 4 of 6]

This is part 4 of 6 on Profit & Loss Reporting using Excel series, written by Yogesh Data sheet structure for Preparing P&L using Pivot Tables Preparing Pivot Table P&L using Data sheet Adding Calculated Fields to Pivot Table P&L Exploring Pivot Table P&L Reports Quarterly and Half yearly Profit Loss Reports in Excel Budget […]

Continue »

What is Excel SUBTOTAL formula and 5 reasons why you should use it

Published on Feb 9, 2010 in Learn Excel
What is Excel SUBTOTAL formula and 5 reasons why you should use it

Today we will learn Excel SUBTOTAL formula and 5 beautiful reasons why you should give it a try.

SUBTOTAL formula is used to find out subtotal of a given range of cells. You give SUBTOTAL two things – (1) a range of data (2) type of subtotal. In return, SUBTOTAL will give you the subtotal for that data. Unlike SUM, AVERAGE, COUNT etc. which do one thing and only one thing, SUBTOTAL is versatile. You can use it to sum up, average, count a bunch of cells.

Continue »

10 Supercool UI Improvements in Excel 2010

Published on Nov 20, 2009 in Learn Excel
10 Supercool UI Improvements in Excel 2010

Today I want to introduce Excel 2010 to you. Excel 2010 is the latest version of spreadsheet software from Microsoft, set to be released for sale in late 2010. On Nov 18th, MS released the public beta of Excel 2010 [download here] along with other Office productivity software.

Excel 2010 has several improvements compared to earlier version – Excel 2007. In this post, I want to highlight some of the User Interface improvements made in Excel 2010 that are very exciting and fun to use.

Continue »