fbpx

All articles with 'videos' Tag

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 »

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 »

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 »

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 »

Multiple Find Replace with Power Query List.Accumulate()

Published on May 14, 2020 in Power Query
Multiple Find Replace with Power Query List.Accumulate()

Imagine you have a paragraph of text and you want to replace all occurrences of {four, normal, mysterious, nonsense} with {six, casual, confounding, handbags}. How would you do that?

You could use SUBSTITUTE() formula, but you need to nest four of them (as we need to replace four values with another four). But what if you have larger set of find / replacements?

Worry not, you can use Power Query to transform original text to new one by replacing all matching values.

In this page, learn how to do that with the excellent List.Accumulate() Power Query function.

Continue »

How to show positive / negative colors in area charts? [Quick tip]

Published on May 12, 2020 in Charts and Graphs, Excel Howtos
How to show positive / negative colors in area charts? [Quick tip]

Ever wanted to make an area chart with up down colors, something like this? Then this tip is for you.

Continue »

Easy Website Metrics Dashboard with Excel

Published on Apr 28, 2020 in Charts and Graphs, Learn Excel, Templates
Easy Website Metrics Dashboard with Excel

Do you run an e-commerce website? You are going to love this simple, clear and easy website metrics dashboard. You can track 15 metrics (KPIs) and visualize their performance. The best part, it takes no more than 15 minutes to setup and use. Here is a preview of the dashboard.

Click to download the template.

Continue »

Add slope line to XY charts

Published on Apr 21, 2020 in Charts and Graphs
Add slope line to XY charts

Slope line is very useful for spotting which values have changed from two sets. You can add a slope line to XY chart (scatter plot) using simple techniques. In this post, learn how to add them.

Continue »

Excel Dynamic Array Functions – What are they, how to use them, Examples and FAQs

Published on Apr 6, 2020 in Learn Excel
Excel Dynamic Array Functions – What are they, how to use them, Examples and FAQs

Excel Dynamic Array Functions are a true game changer. These newly introduced DA functions can filter, sort, remove duplicates and do much more. The output of these functions can go to a range of cells. Hence the name – dynamic array functions.

Continue »

Number to Words – Excel Formula

Published on Mar 24, 2020 in Excel Howtos, VBA Macros
Number to Words – Excel Formula

Ever wanted to convert numbers to words in Excel? For example, 456,123 becomes four hundred fifty-six thousand one hundred twenty-three.

Microsoft recently introduced LET function to Excel. This allows us to create variables on the fly to use within a formula. I just made a words from number formula using LET function and bit of good old INDEX.

Read on to learn how this all works.

Continue »

18 Tips to Make you an Excel Formatting Pro

Published on Mar 19, 2020 in Excel Howtos, Learn Excel
18 Tips to Make you an Excel Formatting Pro

We can take any Excel workbook and format it until Christmas, and we would still not be done. But not many of us have so much of time or energy. So, today, lets talk formatting.

In this, you will learn how to
1) Use tables to format data quickly
2) Change colors of your worksheet in a snap
3) Use cell styles
4) Quickly clone formatting using format painter
5) Clear formats to begin with a clean-slate
6) Formatting shortcuts
7) Formatting options for print
and 8 ) Why you should not go overboard formatting and 10 other tips.

So go ahead and become a formatting pro.

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 »

Advanced Pivot Table Tricks for you

Advanced Pivot Table Tricks for you

Excel Pivot tables make data analysis and visualization easy. With the help of these advanced pivot table skills, you can create powerful data analytics and reports.   New to Pivot Tables? If you are new to Pivot Tables, check out this excellent introduction to Pivot Tables page. × Dismiss alert Table of Contents #1 – One Slicer, Two […]

Continue »