fbpx

All articles with 'Microsoft Excel Conditional Formatting' Tag

Project Plan – Gantt Chart with drill-down capability [Templates]

Published on Jul 29, 2020 in Learn Excel, Templates
Project Plan – Gantt Chart with drill-down capability [Templates]

Create a gantt chart with ability to filter by levels. This drill-down gantt chart in Excel is useful for seeing activities by module or team. Read on for full instructions + downloadable template.

Continue »

Highlight due dates in Excel – Show items due, overdue and completed in different colors

Published on May 18, 2020 in Excel Howtos, Learn Excel
Highlight due dates in Excel – Show items due, overdue and completed in different colors

Congratulations to you if your job does not involve dead lines. For the rest of us, deadlines are the sole motivation for working (barring free internet & the coffee machine in 2nd floor, of course). So today, lets talk about a very familiar problem.

How to highlight due dates in Excel?

The item can be an invoice, a to do activity, a project or anything. So how would you do it using Excel?

Continue »

6 Best charts to show % progress against goal

Published on May 8, 2020 in Charts and Graphs, Learn Excel
6 Best charts to show % progress against goal

Back when I was working as a project lead, everyday my project manager would ask me the same question.

“Chandoo, whats the progress?”

He was so punctual about it, even on days when our coffee machine wasn’t working.

As you can see, tracking progress is an obsession we all have. At this very moment, if you pay close attention, you can hear mouse clicks of thousands of analysts and managers all over the world making project progress charts.

So today, lets talk about best charts to show % progress against a goal.

Continue »

What are Excel Sparklines & How to use them? 5 Secret Tips

Published on Apr 23, 2020 in Charts and Graphs
What are Excel Sparklines & How to use them? 5 Secret Tips

Of all the charting features in Excel, Sparklines are my absolute favorite. These bite-sized graphs can fit in a cell and show powerful insights. Edward Tufte coined the term sparkline and defined it as,

intense, simple, word-sized graphics

Sparklines (often called as micro-charts) add rich visualization capability to tabular data without taking too much space. This page provides a complete tutorial on Excel sparklines.

Continue »

How-to create an elegant, fun & useful Excel Tracker – Step by Step Tutorial

Published on Mar 17, 2020 in Excel Howtos, Templates
How-to create an elegant, fun & useful Excel Tracker – Step by Step Tutorial

Do you want to create a simple, elegant and useful tracker using Excel? You can make trackers with features like tables, data validation rules and conditional formatting. In this page, I will explain the process for creating an Excel tracker.

Continue »

Using IRR with Data Tables – Modeling Cash-flow Scenarios in Excel

Published on Oct 16, 2019 in Analytics, Financial Modeling, Learn Excel
Using IRR with Data Tables – Modeling Cash-flow Scenarios in Excel

Do you want to simulate multiple cash-flow scenarios and calculate the rate of return? Then this article is for you. In this page, learn how to,

  • Introduction to IRR & XIRR functions
  • Calculate rate of return from a set of cash-flows with XIRR
  • Simulating purchase or terminal value changes with data tables
  • Apply conditional formatting to visualize the outputs
  • Common issues and challenges faced when using XIRR
Continue »

Free Excel Risk Map Template

Published on Aug 28, 2019 in Charts and Graphs, Project Management
Free Excel Risk Map Template

Risk comes from not knowing what you are doing. Warren Buffet If you ever ask a project manager what they are up to, they will tell you “I have no idea“. So risks are quite common in project management. That is why I made this awesome free Excel risk map template to keep track and […]

Continue »

Pivot Tables from large data-sets – 5 examples

Published on Aug 2, 2019 in Pivot Tables & Charts
Pivot Tables from large data-sets – 5 examples

Let’s say you are starting at a large dataset with multiple columns. You need to make a pivot report from it for a client or manager. How would you go about it?

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 »

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

Published on Jun 20, 2018 in Excel Howtos, Learn Excel
How to use Date & Time values in Excel – a handy guide

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 »

FIFA Worldcup 2018 Excel Tracker – FREE Download

Published on Jun 7, 2018 in Power Query, Templates
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
Continue »

60 sports in six charts

Published on May 14, 2018 in Charts and Graphs
60 sports in six charts

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.

Continue »

Visualizing Commonwealth games performance – Interactive chart

Published on Apr 13, 2018 in Charts and Graphs, Power Query
Visualizing Commonwealth games performance – Interactive chart

The 2018 edition of Commonwealth games are on for a week now. Both of my homes – India and New Zealand have been doing so well. Naturally, I wanted to gather games data and make something fun and creative from it. Here is my attempt to amuse you on this Friday.

Looks interesting? Want to know how to make something like this on your own? Then read on…

Continue »

Maths worksheets for your kids – teach addition, subtraction & multiplication with these

Published on Apr 9, 2018 in Templates

Nishanth & Nakshatra, my kids are now 8 years old and learning mathematics at school. Very soon (from this Saturday), they will have 2 weeks of school term break. As a stay at home dad, I shudder at the thought of school holidays. So this time, I have an evil plan. I made maths worksheets for them. Everyday, I will print one and ask them to complete.

If you are a stay at home parent and want to keep your kids busy for a few minutes every day, grab a copy of this and give it a go. Keep in mind that the workbook uses RANDBETWEEN(), so the numbers change every time. If you have multiple kids, print multiple copies. There are no answer sheets, so when you need to check the answers, you too must calculate them by hand.

Continue »