fbpx

All articles with 'screencasts' Tag

How-to highlight maximum value in Excel charts? [Quick tip]

Published on Aug 7, 2019 in Charts and Graphs
How-to highlight maximum value in Excel charts? [Quick tip]

Ever wanted to highlight maximum value in charts? Then this tip is for you.

Continue »

How to conditionally format visuals in Power BI?

Published on Jul 30, 2019 in Power BI
How to conditionally format visuals in Power BI?

Do you know that you can apply conditional formatting rules to visuals in Power BI? In this post, let’s learn how to conditionally format visuals in Power BI. Something like this:

Continue »

Tour de France – Distance & Pace over time – Radial Charts

Tour de France – Distance & Pace over time – Radial Charts

This is an Excel replica of excellent Tableau visual on Tour de France winner data made by Marc Reid.

Last week I saw a stunning visualization on Tour de France using radial charts. I wanted to replicate it in Excel. So here we go.

Continue »

How to fake “Key influencers chart” in Excel?

Published on May 1, 2019 in Charts and Graphs
How to fake “Key influencers  chart” in Excel?

Recently, Microsoft Power BI introduced a very useful visualization, called key influencers visualization. As the name suggests, this is a chart of key parameters that effect a measure or outcome.

For example, you have customer satisfaction rating as a measure. Now you want to know which aspects of your data impact the ratings most? You can create the key influencer visual and Power BI finds all the top ranking influencers (using rules and machine learning).

But can we make it in Excel?

Let’s see…

Continue »

How to sort left to right in Excel (quick tip)

Published on Apr 26, 2019 in Learn Excel
How to sort left to right in Excel (quick tip)

Imagine you are in a life sustaining planet named Pearth, in another galaxy. One day you got to work, fired up Excel (hey, what else would you use? Excel is the best data software in any galaxy 🙂 ) and started working.

You came across a dataset that need sorting, but left to right – horizontally.

Now what? Do you turn your monitor sideways?

Continue »

Source vs. Use of Funds – 14 charting alternatives

Published on Apr 10, 2019 in Charts and Graphs
Source vs. Use of Funds – 14 charting alternatives

Let’s say you manage a fund or charity. You get money from various places and you use that money for various reasons. How do you tell the story of source vs. uses of funds? In this post, let’s review 14 charting options. Source vs. Use of funds – Sample Data for this problem Let’s say […]

Continue »

Create impressive dashboard tiles in Excel

Create impressive dashboard tiles in Excel

If you want to tell the story of how your business / project / charity / thing is going on, then making a dashboard is the best way to go about it. Dashboards can combine heaps of data, insights and messages in to one concise format that fits on to a desktop or table or mobile screen.

But let’s be honest. Creating them in Excel is a lot of work. Even after spending hours on them, they might still look meh. So, let me share a trick to make your dashboards look snazzy (without compromising on insights per inch).

Create dashboard tiles, something like above.

Continue »

Employee Turnover Dashboard – Power BI for HR

Published on Mar 8, 2019 in Power BI, Power Pivot, Power Query
Employee Turnover Dashboard – Power BI for HR

Work in HR and use Power BI? You are going to love this extensive, powerful and useful Employee Turnover Dashboard. In this detailed article, learn how to create your own attrition dashboard system with Power BI. Full example workbook, video and sample data included.

Continue »

Quickly change charts from one to another with this trick

Published on Feb 5, 2019 in Charts and Graphs, Quick Tip
Quickly change charts from one to another with this trick

We all change minds. Heck, I just did it with the leading line. I thought something else, but went with what you just read. So why must Excel charts be set in stone? Let’s say you made some charts and want to quickly second guess your selection. How to change Excel chart easily? Sure, you […]

Continue »

Quick tip: Make a list of numbers (or dates) in Power Query easily

Published on Nov 13, 2018 in Power Query
Quick tip: Make a list of numbers (or dates) in Power Query easily

Just a quick tip to revive the blog from a month long silence. I am alive and kicking. I have been occupied with a quest to rescue princess & maidens on video game console. Recently we bought SNES classic console from Nintendo and I have been playing Legend of Zelda – a link to past regularly. As it is almost summer, I am also enjoying the beautiful outdoors in Wellington. All this means, little time for blogging. I will try to post a few more times before the end of year.

Make a list of numbers in a jiffy with Power Query:

We know that in Excel, you can type a few numbers and use the fill handle to fill down (or up etc.) numbers as you want.

But what if you need some numbers in Power Query?

Continue »

Lenient lookup [Advanced Formula Trick]

Published on Sep 13, 2018 in Excel Howtos, Learn Excel
Lenient lookup [Advanced Formula Trick]

We all know VLOOKUP (or INDEX+MATCH) as an indispensable tool in our Excel toolbox. But what if you want the lookups to be a little gentler, nicer and relaxed?

Let’s say you want to lookup the amount $330.50 against a list of payments. There is no exact match, but if we look 50 cents in either direction, then we can find a match. Here is a demo of what I mean.

Unfortunately, you can’t convince VLOOKUP to act nice.

Hey VLOOKUP, I know you are awesome and all, but can you cut me some slack here? 

VLOOKUP is tough, reliable and has a cold heart. Or is it?

In this post, let’s learn how to do lenient lookups.

Continue »

My top 5 tips for designing beautiful Power BI reports

Published on Sep 10, 2018 in Power BI
My top 5 tips for designing beautiful Power BI reports

Power BI allows you to create rich, interactive and informative reports. But it is also a massive pain to create beautiful yet functional reports with Power BI. Over the last 12 months of heavy usage, I have picked up a few tricks to speed up my Power BI report design time. In this post, let me share my top Power BI design tips for creating pretty reports.

Continue »

Make funky and creative hand-drawn chart in Excel – Quick tutorial

Published on Aug 16, 2018 in Charts and Graphs
Make funky and creative hand-drawn chart in Excel – Quick tutorial

Charts are great way to tell story about what is going on in your business. But they also feel a bit too impersonal and meh. How about adding your personality to them? I don’t mean making them tall, dark and pretty. I mean using hand-drawing style to make them pop out. Something like above example of hand-drawn chart.

The best thing is, You don’t need to actually draw these charts by hand. We can use a powerful charting trick to get these charts automatically generated (and linked) to your data. Interested? Read on to learn how to create hand-drawn charts in Excel.

Continue »

Play spreadsheet soccer with Excel Penalty Game [VBA]

Published on Jul 4, 2018 in Charts and Graphs, VBA Macros
Play spreadsheet soccer with Excel Penalty Game [VBA]

We love spreadsheets. And of course, once every four years, we also get mad about soccer. So why not merge both of them in to one awesome, frivolous and fun thing: Introducing….

Excel soccer game

The best part is you don’t have to run up to play this. Set your aim and let RANDBETWEEN() decide your fate.

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 »