All articles with 'screencasts' Tag

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.

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.

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….

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

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

We all know that learning a few keyboard shortcuts can speedup your Excel game. Most pro users rely on a handful shortcuts when working with large spreadsheets. But when it comes to charting, we automatically reach for mouse. But do you know that you can use few simple shortcuts to do most day to day chart related things?

Ready for top 5 keyboard shortcuts for Excel charts? Read on.

Ever wanted to make a cool, snazzy interactive chart in Excel? Something like this:

In this tutorial, learn all about making your very first interactive chart. We use both formulas and pivot tables to build two versions of an awesome interactive chart in Excel.

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…

Pivot tables are fun, easy and super useful. Except, they can be ugly when it comes to presentation. Here is a quick way to make a pivot look more like a report.

• Just type over the headers / total fields to make them user friendly.

See this quick demo to understand what I mean:

So simple and effective.

Yesterday, my mate from across the ditch, Hui posted about conditional rank formula (RANKIFS) using awesome SUMPRODUCT

Of course, not everyone can whip up a sumproduct formula like that. On a scale of One to Hui of Excel awesomeness, you would need to be at least an H to write sumproduct or countifs formulas shown in that post. So does it mean, you can’t conditional rank if you don’t know your X from L?

Don’t worry. We got you covered. You can still get your conditional ranks, without inception level array formulas. Simple, use pivot tables instead.

Time for some statistics and charting fun. Let’s learn all about histograms and Pareto charts in Excel 2016. You will learn

• What, why and when?
• How to set up and customize histograms
• How to use Pareto charts?
• How to create dynamic histograms?
• Creating histograms in old Excel (2013 or prior versions)

Sounds interesting? Let’s get started then.

We are in the midst of my Power BI Play Date course launch. I have opened the enrollments for this program last week and there is a tremendous response to this program.  To celebrate the new course launch and show you the lighter side of it, let me share a few breathing exercises built in Power BI.

Time for some good, old fashioned VLOOKUP love. Let’s say you are writing VLOOKUP()s to get data from an unusually fat table, ie one with heaps of columns. You want to get to lookup ID in first column and get thingamajig in what is that column number. Well, better get counting from 1 and after 19 seconds and lots of squinting you arrive at column number 53 – which has thingamajig. 

If this sounds like your VLOOKUP routine, check out these three amazingly simple tips to save some time and effort with your lookups.

Game for some charting awesomeness?

Off late, I have been doing a lot of data analysis and visualization on performance ratings, salary hike, gender pay equality etc. Today let me share you an awesome way to visualize massive amounts of data.

Scenario: Your organization of 3,686 people recently went thru annual performance ratings & review process. At the end of it, everyone was offered some salary increase (from $0 to $24,000 per year). You have 7 business groups. How do you tell the story of all these salary hikes in one chart?

How about the one above?

Ready to know how to create this in Excel? Read on.

Yesterday we saw a beautiful example of panel charts with R. Today let me show you how to create the same (or even better) with Power BI & R. What you need: Power BI Desktop and R Raw data set – rem-data.csv Creating Panel Charts in Power BI with R Load CSV data in to […]

Ever had a workbook with multiple protected worksheets? May be you are enterprise architect at Death Star or chief strategist at Mordor and got all the plans in a tidy little but protected workbook. Of course, you hate having to unprotect many of the worksheets every time you have a new evil plan for world domination. Don’t you worry, you can use this handy little trick to unproect en masse.

• Just open the workbook
• Go to File > Info
• Right on the top, you can see all protected worksheets and a link to unprotect them.
• Click to unprotect the ones you want to.
• Done.