Author Archive

Excel 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.

Time for some soccer fun in spreadsheets. Given a team points table like above,

How would you figure out in Excel, which teams qualify for next stage. The rules for this exercise are,

• Pick the top two teams by points
• If there is a tie, use Goal Difference (GD) to break ties (more GD is good)
• If there is a tie, use Goals For (GF) to break ties

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!”

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:

• Excel Books
• Books for data & visualization
• Power BI Books
• Excel VBA Books
• My own books

FIFA world cup 2018 is around the corner. I love soccer, I love Excel, Let’s marry them. Here is an awesome, free FIFA world cup Excel Tracker to help you follow this year’s games in Russia.

Click here to download the FIFA worldcup 2018 tracker.

What you can do with this FIFA world cup Tracker Excel?

You can use this tracker to,

• View schedules in your local time for group and knockout stages
• View summary and detailed points table
• Refresh live points table. When you refresh, the tracker show updated points based on latest results (You need Excel 2016, Office 365 or older versions of Excel with Power Query)
• View knockout stage matches as a bracket
• See timeline of the matches

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

Ever wanted to count distinct values in your pivot tables? Something like above:

Let’s say you have store sales data. Several products are sold on each day. When you make a pivot table from this data and add product count, Excel counts all products. But we want to see just the distinct count (ie if there is a duplicate product in a day, we want to count it just once).

Here is a simple trick to add distinct count to Excel pivot tables easily.

Call them by any name – Budget vs. Actual, Target vs. Actual, Goal vs. Progress, KPIs, Performance charts, but they are the bread and butter of business charting. So how about a drop dead gorgeous and insightful chart for your next meeting with the folks upstairs? Something like above.

Read on to learn how to create this chart in Excel.

On twitter I follow many charting and visualization related accounts. One of them is @Andy Kriebel, who runs Makeover Monday. The idea is simple. Every Monday they publish a data-set and ask the community to visualize. Last Monday (7th May, 2018), they have published about toughest sport by skill data. This categorizes 60 sports by 10 skill categories to find out which sport is the toughest. Over the weekend, Andy posted a summary of all toughest sport viz entries. Many of the entries are made in Tableau. I thought it would be a fun challenge to re-create some of these charts in Excel. The result is this post. 60 sports in 6 charts. Check out the charts and download workbook to learn more.

First four charts are re-creations of Tableau designs. Last two are mine.

It is star wars day. Let’s celebrate the ultimate nerd day of the known universe in a way that resonates with us. Let’s dress up as storm troopers and make exploding 3d pie charts.

Just kidding. Let’s just make a cool visualization in Power BI instead.

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.

We all know that Pivot Tables are best thing since avocado on toast. But they can’t slice text values and spread them in a table with Pivots. So how to take a large blob of text and turn it in to something meaningful like above?

Simple, we use Power Query.

Are you hungry from some Excel awesomeness? It’s launch time. I am very happy to announce that our most popular analytics online class – 50 ways to analyze data is opening up for enrollments in first week of May 2018. As the name suggests, 50 ways program is all about data and making sense of it. You will learn many ways to analyze, visualize and present data to your clients and superiors. Next time someone asks you, “Can you look at this data and tell us what is going on?” you can confidently say YES.

Learn all about 50 ways program and signup for the waiting list.

In this article, Let me show case one of the examples from 50 ways to analyze data course and how it can help you.

We all know that learning a few keyboard shortcuts can speedup your Excel game. Most pro users rely on a handful shortcuts when working with large spreadsheets. But when it comes to charting, we automatically reach for mouse. But do you know that you can use few simple shortcuts to do most day to day chart related things?

Ready for top 5 keyboard shortcuts for Excel charts? Read on.

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.