All articles with 'videos' Tag
I recently went to Sydney to conduct some training programs on Advanced Excel and Power BI. While I was there, I met my good friend Danielle, who runs Plum Solutions, a financial modeling consultancy & training company. We got talking about various things and the topic eventually turned to “finance people and Power BI”. We […]Continue »
Here is a very useful and almost secret Excel tip for you. Imagine you are looking at a big, complex workbook with lots of calculations. You want to understand where everything is pointing to and how the workbook is set up.
You can use trace precedents in Excel to do this. Read this tip to learn how it works.Continue »
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 »
We all know that VLOOKUP can find first match and return the results. But what if you want all the matches? Use this simple trick instead.Continue »
Excel has hundreds of formulas. But as a new learner or user, you may want to just focus on top 10 formulas to get the most out of it. Assuming you already know the basics (check out Beginner Excel page if you are complete newbie), here is a list of top 10 Excel formulas for you.Continue »
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 »
Last Friday, I asked you to analyze “sick leave entitlement vs. usage” data and answer homework questions. We got several interesting responses to that. Today, let me share a quick video highlighting how to analyze such data with Power Query.
This is part of our Power Mondays series, where every Monday you will learn something new & useful about Power BI, Power Query and Power Pivot.Continue »
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 »
Do you want to learn Advanced Excel, data analysis, creative charting, dashboard reporting and be productive in Excel? Then you are going to love my Excel School v2.0.
This is the golden age of data. Any rewarding job or entrepreneurship requires good data literacy. We are all expected to understand the relevance of data, how to manage it, how to analyze it, visualize it and tell elegant stories.
This is where Excel School comes in.Continue »
Ever wondered how to get percentage of another value in Excel pivot tables, like this: In this tip, learn how to create such calculations using Excel pivot tables. Note: this tip is not compatible with older versions of Excel. If you are using Excel 2007 / 2010 / 2013, then please install free Power Pivot […]Continue »
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 »
Ever wanted to calculate distance using Excel – between two locations (physical addresses)? If we know the addresses, we can go to either Google Maps or Bing Maps and type them out to find the distance and travel time. But what if you are building some model (or calculator) and want to find out the […]Continue »
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.Continue »
Ever wondered how to undo in Power Query. If you try to press CTRL+Z or look for undo icon in Power Query (either in Excel or Power BI), you will not find it. The reason is simple. There is no undo in Power Query. So how to undo ?Continue »
It is star wars day. Let’s celebrate the ultimate nerd day of the known universe in a way that resonates with us. Let’s dress up as storm troopers and make exploding 3d pie charts.
Just kidding. Let’s just make a cool visualization in Power BI instead.Continue »