All articles in 'Learn Excel' Category

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 »

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 »