fbpx

Author Archive

Two-level Data Validation [Excel Trick]

Published on Sep 23, 2020 in Excel Howtos, Learn Excel
Two-level Data Validation [Excel Trick]

Ever wanted to create a two-level data validation list? You can use this simple trick to make two-level or cascading drop-down validation lists in Excel. You need some data, a pivot table and simple IF formulas to get this. Read on to understand the process and create your own two-level drop down lists in Excel.

Continue »

Excel formula to convert calendar format to table

Published on Sep 3, 2020 in Learn Excel
Excel formula to convert calendar format to table

Got some data in a calendar format and just wished you can get it in tabular format?

You can use Excel formulas or Power Query to do this. In this article, let’s review formula based approach with two excellent choices.

Continue »

Power Query Tutorial – What is it, How to use, Full examples, Tips & Tricks

Published on Aug 26, 2020 in Power Query
Power Query Tutorial – What is it, How to use, Full examples, Tips & Tricks

Power Query (Get & Transform data in Excel) is a true game changer ?. It can simplify and automate various data activities. In this in-depth blog tutorial, learn all about this powerful time-saving technology. You will learn:

* What is Power Query and how to launch it from Excel or Power BI?
* How to connect and load data from any type of sources – excel files, CSVs, text files, web pages, PDF, folders etc.
* How to perform common data cleansing activities with Power Query
* Adding columns using rules
* Automating data collection, clean-up and publishing processes with Power Query
* Web scraping with Power Query
* SQL style Joins, merges, appends and unions with Power Query
* Consolidating data in a folder
* Data load, save & publish process
* Tips & tricks on using Power Query efficiently

Continue »

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 »

Introducing Power BI Play Date – Online class to master Power BI (oh yeah, Power Query & Power Pivot too)

Published on Jul 22, 2020 in Power BI
Introducing Power BI Play Date  – Online class to master Power BI (oh yeah, Power Query & Power Pivot too)

Hello friends,

It’s here. Power BI Play Date – our online training program is now ready for your consideration. Please take a few minutes to read this to know all about the program. If you are ready to join, click here.

Check out below slideshow to see some of the reports you can create in our course. It is #AWESOME.

Continue »

How to make stream graphs in Excel?

Published on Jul 16, 2020 in Charts and Graphs
How to make stream graphs in Excel?

A stream graph or stream plot is an area chart that looks like a stream. In this post, let me show you how to create an interactive stream graph using Excel area charts. The original design for this graph is inspired from Cedric Scherer.

Continue »

These Pivot Table tricks massively save your time

Published on Jul 6, 2020 in Learn Excel, Pivot Tables & Charts
These Pivot Table tricks massively save your time

Pivot tables are powerful. Use these 6 tricks to save time when working with them. New to Pivot Tables? Check out this intro Are you new to Pivot Tables or just used them a few times? Then check out this excellent getting started with Pivot Tables guide. 1 – Double click to see details Ever […]

Continue »

50% done – mid year updates for 2020

Published on Jul 2, 2020 in personal
50% done – mid year updates for 2020

Hello folks. 50% of 2020 is behind us. Let me share a few updates with you all. First check out this awesome double rainbow outside our house on 30th June. MVP I am thrilled and humbled to tell you that I have been re-awarded Microsoft MVP for 2020 year. This is my 12th year in […]

Continue »

Top 10 Excel formulas for IT people

Published on Jun 18, 2020 in Learn Excel, Project Management
Top 10 Excel formulas for IT people

Are you in IT & use Excel often? This article explains top 10 formulas for IT professionals. Useful for project managers, IT analysts, Testing people and BAs.

We cover a 10 practical situations and explore various Excel formulas to solve them. Example workbook provides more details too.

Continue »

Lookup last non blank value – Excel Challenge

Published on Jun 12, 2020 in Excel Challenges, Learn Excel
Lookup last non blank value – Excel Challenge

I have a fun Excel lookup challenge for you. You have data as shown below and want to find the last non blank value for a given account number. For example, for acct number 2015, the answer would be Freedom. How would you solve this? Refer to this workbook for 3 possible answers. Just move […]

Continue »

Excel TEXTJOIN Function – What is it, how to use it & 3 advanced examples

Published on Jun 9, 2020 in Learn Excel
Excel TEXTJOIN Function – What is it, how to use it & 3 advanced examples

Use TEXTJOIN function to combine text values with optional delimiter. It is better than CONCATENATE because you can pass a range instead of individual cells and you can ignore empty cells too. Here is a sample use of TEXTJOIN Excel function.

Continue »

How to make a variance chart in Power BI? [Easy & Clean]

How to make a variance chart in Power BI? [Easy & Clean]

Power BI is great for visualizing and interacting with your data. In this article, let me share a technique for creating variance chart in Power BI. Variance charts are perfect for visualizing performance by comparing Plan vs. Actual or Budget vs. Actual data.

Continue »

How to make an Interactive Chart Slider Thingy

How to make an Interactive Chart Slider Thingy

Ok, I will be honest. I have no idea what to call it. May be Chart Cover Flow? But Interactive Chart Slider Thingy sounds so better. So let’s go with it.

Learn how to create this magical contraption in Excel.

Continue »

How to export YouTube video comments to Excel file? – Free template + Power Query case study

Published on May 28, 2020 in Power Query
How to export YouTube video comments to Excel file? – Free template + Power Query case study

This week, I am running a contest on YouTube. One of the criteria for picking winners is that they must comment on my video. So far, I got more than 200 comments. To make my job easier, I want to export the video comments to an Excel file. Turns out this is easily done once you have a Google developer API key. In this article, let me explain the process for extracting Youtube video comments to Excel table.

Continue »

Celebrating 50k Subscribers on YouTube + Give away

Published on May 25, 2020 in personal
Celebrating 50k Subscribers on YouTube + Give away

Hiya folks… Got an exciting news to share with you all. Over the weekend, my YouTube channel hit 50,000 subscriber milestone.

Thank you so much for making me a part of your journey to awesomeness.

Continue »