All articles in 'Learn Excel' Category

Introduction to Excel SUMIFS Formula

Published on May 25, 2018 in Learn Excel
Introduction to Excel SUMIFS Formula

Excel SUMIFS function is used to calculate the sum of values that meet any criteria. For example, you can calculate the total sales in east zone for product Pod Gun using SUMIFS formula.
In this article, you will learn:

  • What is SUMIFS function and how to use it?
  • Syntax for SUMIFS
  • Using SUMIFS() with tables and structural references
  • SUMIFS examples – simple, wild card
  • Using SUMIFS() with date & time values
  • Free sample file for SUMIFS formula
  • More formulas for data analysis
Continue »

35 shortcuts & tricks to make you an #AWESOME Data Analyst

35 shortcuts & tricks to make you an #AWESOME Data Analyst

Analyst’s life is busy. We have to gather data, clean it up, analyze it, dig the stories buried in it, present them, convince our bosses about the truth, gather more evidence, run tests, simulations or scenarios, share more insights, grab a cup of coffee and start all over again with a different problem.

So today let me share with you 35 shortcuts, productivity hacks and tricks to help you be even more awesome.

Continue »

Create your first interactive chart in Excel with this tutorial

Published on Apr 19, 2018 in Charts and Graphs, Learn Excel, Pivot Tables & Charts
Create your first interactive chart in Excel with this tutorial

Ever wanted to make a cool, snazzy interactive chart in Excel? Something like this:

In this tutorial, learn all about making your very first interactive chart. We use both formulas and pivot tables to build two versions of an awesome interactive chart in Excel.

Continue »

Rescue oddly shaped data – Battle between Formulas, VBA and Power Query

Published on Apr 11, 2018 in Learn Excel, Power Query, VBA Macros
Rescue oddly shaped data – Battle between Formulas, VBA and Power Query

Let’s say you have data like this in a spreadsheet. Don’t roll your eyes, I am 102% sure, right at this moment, someone is (ab)using Excel to create similar messy data.

How do you reshape it to one column?

You could use formulas, VBA or Power Query. Let’s examine all these methods to see what is best. All these methods assume your data is in a range aptly named myrange.

Continue »

Stay on top of money with this awesome household budget spreadsheet [downloads]

Published on Mar 1, 2018 in Learn Excel, Templates

I believe in frugal living and paying yourself first. One of the simple ways to achieve this is by using a budget. You know how much money you get. Once you can track (or estimate) how much you are spending, it is easy to see how much you are paying your future self and what wiggle room you have. So in the spirit of making you awesome in life, not just Excel, let me share a simple but elegant household budget spreadsheet.
Here is a screenshot of the budget.

Continue »

vlookup(), MATCH() and INDEX() – explained in plain English [spreadcheats]

Published on Feb 10, 2018 in Featured, Learn Excel
vlookup(), MATCH() and INDEX() – explained in plain English [spreadcheats]

VLOOKUP may not make you tall, rich and famous, but learning it can certainly give you wings. It makes you to connect two different tabular lists and saves a ton of time. In my opinion understanding VLOOKUP, INDEX and MATCH worksheet formulas can transform you from normal excel user to a data processing beast. Today, […]

Continue »

This year, become a Very Table Genius

Published on Jan 11, 2018 in Learn Excel
This year, become a Very Table Genius

Let’s start the new year with a bang.

Excel Tables were introduced more than a decade ago, but a lot of people don’t know them or under utilize them. So start this year by becoming a very table genius.

What is Excel Table?

Excel tables are a simple and elegant way to structure and store your data. Let’s say you have staff details like below. Instead of calling it like A1:E72, you can convert this data in to a table and call it, you guessed it right, covfefe (or more coherent option like – staff).

Continue »

2018 Calendar, daily planner Excel templates [free downloads]

Published on Jan 3, 2018 in Learn Excel

Here is a New year gift to all our readers – free 2018 Excel Calendar & daily planner Template.

This calendar has,

  • One page full calendar with notes, in 4 different color schemes
  • Daily event planner & tracker
  • 1 Mini calendar
  • Monthly calendar (prints to 12 pages)
  • Works for any year, just change year in Full tab.
Continue »

5 conditional formatting top tips – Excel basics

Published on Dec 5, 2017 in Excel Howtos, Learn Excel
5 conditional formatting top tips – Excel basics

Time for another round of unconditional love. Today, let’s learn about conditional formatting top tips. It is one of the most useful and powerful features in Excel. With just a few clicks of conditional formatting you can add powerful insights to your data. Ready to learn the top tips? Read on.

Continue »

VLOOKUP that fat table with ease [3 quick tips]

Published on Sep 18, 2017 in Learn Excel
VLOOKUP that fat table with ease [3 quick tips]

Time for some good, old fashioned VLOOKUP love. Let’s say you are writing VLOOKUP()s to get data from an unusually fat table, ie one with heaps of columns. You want to get to lookup ID in first column and get thingamajig in what is that column number. Well, better get counting from 1 and after 19 seconds and lots of squinting you arrive at column number 53 – which has thingamajig. 

If this sounds like your VLOOKUP routine, check out these three amazingly simple tips to save some time and effort with your lookups.

Continue »

Copy Paste Visible Cells only (Two more ways to do it)

Published on Aug 28, 2017 in Excel Howtos, Keyboard Shortcuts, Learn Excel
Copy Paste Visible Cells only (Two more ways to do it)

Last week, we talked about how to copy and paste visible cells alone (ie exclude any filtered rows or hidden columns etc.) In the comments section many of you suggested two more ways to deal with this annoying problem. Let’s take a look them.

Continue »

Copy & paste visible cells only [Excel Trick]

Published on Aug 24, 2017 in Keyboard Shortcuts, Learn Excel
Copy & paste visible cells only [Excel Trick]

Here is something annoying with Excel.

Open any Excel file with few columns of data. Hide some of those columns (select the columns and press CTRL+0). Now, copy a few rows of data. Paste it else where. Excel will paste the values in hidden columns too. We thought Excel would omit the values in hidden columns.

What the filter Excel?!? I thought we were friends, but you annoy me with some of these quirks.

Continue »

SUMPRODUCT Vs. Power Query on Mt. KauKau

Published on Jun 15, 2017 in Learn Excel, Power Query
SUMPRODUCT Vs. Power Query on Mt. KauKau

When faced with tough problems I react in one of three ways

  1. Come up with ingenious solutions
  2. See if a simpler cheat solution is possible
  3. Sit back and ignore

For most problems, I choose 3rd reaction. Occasionally, I rely on 2nd option and very rarely the first one.

When faced with a tricky time sheet summary problem (as outlined above), after initial lethargy I wanted to solve it.

Continue »

Avoid Hiring Boo-boos with Excel – COUNTIFS for the win [video]

Published on Apr 26, 2017 in Excel Howtos, Learn Excel
Avoid Hiring Boo-boos with Excel – COUNTIFS for the win [video]

Imagine you are head of human resources at Casual Contracting Co. Every month you hire a lot of temporary staff who spend 1-4 months with CCC before leaving. Sometimes you hire the same people again. Of late, you have noticed a strange process gap. You are paying same person two (or more) salaries.

This is because you are hiring a person for new temp role even before their current one ended. See above picture.

So how to avoid making such hiring boo-boos.

Simple, using Excel of course.

Continue »

Relative References in Excel Tables

Published on Apr 21, 2017 in Excel Howtos, Learn Excel
Relative References in Excel Tables

Excel Tables have been around for a decade now (they are introduced in Excel 2007), and yet, very few people use them. They are versatile, easy and elegant. At Chandoo.org, we celebrate Tables all the time. If you have never used them, start with below tuts.

While tables are super helpful, they do come with some limitations. Today let’s examine one such unique problem and learn about an elegant solution.

Continue »