All articles with 'data filters' Tag

Excel Tables Tutorial & 13 Tips for making you a Data Guru

Published on Jun 13, 2018 in Excel Howtos, Featured, Learn Excel
Excel Tables Tutorial & 13 Tips for making you a Data Guru

Excel table is a series of rows and columns with related data that is managed independently. Excel tables, (known as lists in excel 2003) is a very powerful and supercool feature that you must learn if your work involves handling tables of data.

What is an excel table?

Table is your way of telling excel, “look, all this data from A1 to E25 is related. The row 1 has table headers. Right now we just have 24 rows of data. But I can add more later!”

Continue »

Filter all records for November or 11AM or 2017 [quick tip]

Published on Dec 4, 2017 in Quick Tip
Filter all records for November or 11AM or 2017 [quick tip]

Imagine you are the first officer at ship terminal αε974F1 on remote planet Alderaan. Your job involves looking at terminal log to see anomalies in time space continuum. So one day after getting to work late, thanks to crazy traffic on the floating super way in your settlement, you are looking at latest terminal log for αε974F1 on Excel (of course Excel, what else are you going to use? Notepad?!?) and want to check all the records logged at 7 AM on any day. You don’t have all the time in universe to filter records one at a time. You don’t want to write a formula or something else as it is too early in the morning and the nearest Starbucks is 7 light years away. So what would you do?

Use filters of course.

Continue »

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 »