All articles in 'Learn Excel' Category

Lenient lookup [Advanced Formula Trick]

Published on Sep 13, 2018 in Excel Howtos, Learn Excel
Lenient lookup [Advanced Formula Trick]

We all know VLOOKUP (or INDEX+MATCH) as an indispensable tool in our Excel toolbox. But what if you want the lookups to be a little gentler, nicer and relaxed?

Let’s say you want to lookup the amount $330.50 against a list of payments. There is no exact match, but if we look 50 cents in either direction, then we can find a match. Here is a demo of what I mean.

Unfortunately, you can’t convince VLOOKUP to act nice.

Hey VLOOKUP, I know you are awesome and all, but can you cut me some slack here? 

VLOOKUP is tough, reliable and has a cold heart. Or is it?

In this post, let’s learn how to do lenient lookups.

Continue »

Announcing Excel School v2.0 – Quick intro and details inside

Published on Aug 8, 2018 in Learn Excel
Announcing Excel School v2.0 – Quick intro and details inside

Do you want to learn Advanced Excel, data analysis, creative charting, dashboard reporting and be productive in Excel? Then you are going to love my Excel School v2.0.

This is the golden age of data. Any rewarding job or entrepreneurship requires good data literacy. We are all expected to understand the relevance of data, how to manage it, how to analyze it, visualize it and tell elegant stories.

This is where Excel School comes in.

Continue »

12 Steps to learn Excel and become awesome @ work in 2018

Published on Aug 7, 2018 in Learn Excel
12 Steps to learn Excel and become awesome @ work in 2018

Obviously, everywhere you look this week, you find advice on how to do better this year.

Well, you don’t have to wait for a new year to become awesome at your work, any day is new year for the rest of your life.

So, today I want to share 12 steps to learn Excel and becoming awesome. But first I have a secret to confess…,

I think becoming awesome in Excel useless.

What we really want is, to become awesome in our work. Since we spend a lot of time using Excel (and other office software), knowing how to use these better can have a huge impact on how we do our work.

In that spirit, lets look at 12 steps to learn Excel so you become awesome in your work.

Continue »

Top 5 HR Analytics Examples – Free Video Masterclass

Published on Jul 27, 2018 in Learn Excel, Master Class, Power Query
Top 5 HR Analytics Examples – Free Video Masterclass

I recently finished a long consulting gig with one of the government ministries in New Zealand. Guess what I was doing? HR Analytics and Reporting. In this post, I want to share my top 5 Excel tips for HR people, based on what I learned in the last 18 months.

Specifically, we will cover:

  • Gathering and structuring Employee data in Excel
    • How to use Power Query to collect data
    • Polish / clean data in Power Query
    • Bring cleaner data to Excel as refreshable table
  • Answering questions about employees
    • Using Excel formulas such as COUNTIFS, SUMIFS, AVERAGEIFS
    • Pivot tables for data analysis
    • Understanding the results quickly with conditional formatting
  • Understanding pay gap
    • Calculating gender pay gap
    • Visualize pay gap
  • Creating salary distribution charts
    • Working with histogram charts in Excel 2016 / Office 365
    • Making interactive charts
  • Generating letters thru mail merge
    • Calculating employee bonus based on bonus mapping logic
    • Creating 100s of letters with a single click using Mail Merge + Word

Sounds interesting? Read on for details.

Continue »

Mutual Fund Portfolio Tracker using MS Excel

Published on Jul 6, 2018 in Learn Excel, personal finance, Power Query, technology
Mutual Fund Portfolio Tracker using MS Excel

Would you like to spend next 5 minutes learning how to create an mutual fund tracker excel sheet?

Make a live, updatable mutual fund portfolio tracker for Indian markets to keep track of your investments using this example.

Continue »

How to use Date & Time values in Excel – a handy guide

Published on Jun 20, 2018 in Excel Howtos, Learn Excel

How to use Excel Date values, formulas and tipsExcel date time features are very handy and knowing how to use Excel date values can help you save a ton of time in your day to day spreadsheet chores. Let us prepare for your date with the sheet using these 10 handy tips.

Before jumping on to the tips, it helps to know how excel represents the date and time.

Microsoft Excel stores dates as sequential numbers … January 1, 1900 is serial number 1, and 20 June, 2018 is serial number 43271 because it is 43,271 days after January 1, 1900. Excel stores times as decimal fractions because time is considered a portion of a day. [Excel Help Text on Date / Time]

So you see, Date and Time are in fact numbers in Excel. Just enter a date in your excel sheet and format it as number to see its equivalent numeric value. If a date is 20-June-2018 and excel represents it as 43271.

Continue »

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 »

Best Excel Books & Power BI Books – 2018

Published on Jun 11, 2018 in Learn Excel, Power BI, Power Pivot, Power Query
Best Excel Books & Power BI Books – 2018

So you have decided to up your game with Excel and / or Power BI this year and now ravenously looking for books to read. You have come to the right place. Here is my list of recommended best Excel books, and books on Power BI, visualization, dashboards, VBA, Macros and analytics.

Use below links to navigate the relevant section of this page:

Continue »

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 »