direct tag

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 […]

The post Quickly change charts from one to another with this trick appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.

Continue reading

direct tag

direct tag

Elevator problem – Excel homework

The other day while I was in lift (elevator), it made an alarm like sound and won’t close the doors. Turns out there are one too many people in the lift for it to operate safely. As soon as a couple of people volunteered and stepped out, it started fighting gravity and took us upstairs. […]

The post Elevator problem – Excel homework appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.

Continue reading

direct tag

direct tag

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?

The post Quick tip: Make a list of numbers (or dates) in Power Query easily appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.

Continue reading

direct tag

direct tag

Leave entitlement vs. usage analysis with Power Query

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.

The post Leave entitlement vs. usage analysis with Power Query appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.

Continue reading

direct tag

direct tag

How many people used their entire sick leave entitlement? [Power Query / Excel homework]

Imagine you are the HR analyst at BigLargeCompany. You are asked to find out whether staff at BLC (BigLargeCompany you silly) use up their full sick leave entitlement.

You have two tables – emps & leaves as illustrated below.

sample data - sick leave entitlement vs. usage

Your mission is to find out answers to below questions.

  • How many employees used exactly 100% of their entitled sick leave?
  • How many employees did not take any sick leaves?
  • Listing of all employees who used 100% of their entitlement

 

Use either Power Query, Excel formulas or any other technique to answer the questions.

The post How many people used their entire sick leave entitlement? [Power Query / Excel homework] appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.

Continue reading

direct tag

direct tag

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.

The post Lenient lookup [Advanced Formula Trick] appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.

Continue reading

direct tag

direct tag

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.

The post My top 5 tips for designing beautiful Power BI reports appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.

Continue reading

direct tag

direct tag

When is the next Monday? [Homework]

Psst. Hey you, yeah, you there… have time for a quick date? A date with Excel that is.

If so, take up this homework problem and post your answers in comments.

Assuming you have an input date in cell A1,

  • What is the formula for finding next Monday?
  • What is the formula for finding first Monday of next month?

Post your answers in comments

The post When is the next Monday? [Homework] appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.

Continue reading

direct tag

direct tag

What is Power BI, Power Query and Power Pivot?

In this installment of Power Mondays, let’s go the very basics of it all.

What is Power BI?

Power BI is a software to create and publish interactive, web-enabled reports & visualizations for your audience. You can use Power BI on a PC or web to create things. Once you publish a report (or few visuals), your audience can consume them by using any device – PCs, Macs, Web browsers, Apps on cell phone / tablets etc. 

The post What is Power BI, Power Query and Power Pivot? appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.

Continue reading

direct tag

direct tag

5 tip: Power Query for Accountants (and finance people) – Free Masterclass

Do you work with accounting or finance data? You are going to love, no scratch that, adore Power Query. It can save you precious time, make you look like a hero in-front of clients and keep you sane.

In this masterclass, learn all about Power Query for accountants (and other kinds of finance people) & 5 tips.

Why Power Query?

Power Query is a data processing software. Using Power Query, you can create process driven programs (queries) to collect data, clean or reshape it, calculate things and publish final output as a table. Once you create a query in PQ, when the underlying data changes, you just refresh the query and brand new data will be fetched, cleaned and published for you.

For example, you can use Power Query to combine ledger files from various accounts to one master file with Power Query. No more VBA or manual copy pasting. Just one query and you are done.

The post 5 tip: Power Query for Accountants (and finance people) – Free Masterclass appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.

Continue reading

direct tag