Chandoo.org - Learn Excel, Power BI, Charting, Dashboards and VBA online

Excel Howtos

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

How to use Excel Date values, formulas and tips 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.

Read This »

June 20, 2018

Excel Challenges

Which two teams qualify to next stage? [Excel Homework]

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

Read This »

June 15, 2018

Table styles to change the look & feel of your Excel tables

Excel Howtos

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

Read This »

June 13, 2018

Learn Excel

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:

Read This »

June 11, 2018

Power Query

FIFA Worldcup 2018 Excel Tracker – FREE Download

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

Read This »

June 7, 2018

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

Read This »

May 25, 2018

Excel Howtos

Excel formula showing as text instead of actual result – How to fix the problem

Once in a while everyone is bound to come across this problem. You type a formula in a cell, then you press ENTER. Bam! nothing

Read This »

May 24, 2018

Pivot Tables & Charts

Distinct count in Excel pivot tables

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.

Read This »

May 23, 2018