It is Olympic season. Everyone I know is tracking the games and checking their country’s performance. One thing that we notice when looking at medal tally is,

A single Gold medal is worth more than any number of Silver medals. Like wise, a single Silver medal is worth more than any number of Bronze medals.

So, when you look at the ranking of countries, you see countries with single Gold medal higher up than countries with lots of Silver and Bronze medals (but no Gold).

So how do we sort our data in Olympic medal table style?

It is simpler than it looks. All you have to do is use custom sort feature in Excel.

1. Select your data
2. Go to Home > Sort & Filter > Custom Sort
3. Now specify the sort levels and sort orders.
4. Click ok and you are done!

Using SORTBY() formula to sort the table

Excel 365 introduced a new formula to sort data by multiple-levels using formulas. SORTBY

Assuming your medal data is in the table named medals you can sort with below formula.

=SORTBY(medals, medals[Gold],-1, medals[Silver], -1, medals[Bronze],-1,
medals[Team / NOC],1)

The -1 parameter tells SORTBY to sort in descending order.

Learn more about SORTBY function & other new formulas in Excel 365.

What if your version of Excel does not have SORTBY

Well, there is a work around. Add an extra column in your data and calculate sort order using a formula, as shown below.

Once you calculate sort order, sort on this column in descending order and you are done.

Video – Sorting Excel data in Olympic medal table style

Watch this short & fun video to learn the sorting technique outlined in this page.

Example file – Olympics Medal Table style sorting in Excel

Please download this Excel file if you want to practice the custom sort or SORTBY() approach.

Do you use custom sort?

Custom sorting is very useful when you 2-3 levels in your data. For example, sorting all projects by department & % completed or sorting all products by region & sales volume. I use it often to understand how my data is.

What about you? Do you use custom sort? What is your experience like? Please share your tips & thoughts using comments.

More Quick Tips on Sorting & Filtering

If you find yourself constantly sorting and filtering, then check out below tips. I am sure you will learn something new.

• Sorting:
• Custom sorting in Pivot tables
• Which formula to use to check if a list is sorted?
• Formula 1 style sorting in Excel
• How to round and then sort data in Excel
• Sorting text values using formulas!
• Shuffling a list in random order in Excel
• How to sort across columns (ie change sort orientation)
• Filtering:
• Filtering values using advanced filters
• How to filter odd or even rows only?
• Right click to filter fast…

The post Sorting values in Olympic Medal Table style [Quick Tip] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

The post Getting Started with Power BI – Sales Dashboard in one hour appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Excel text functions are useful for cleaning up text / alphanumeric values, extracting parts of cell and presenting combined results in output pages. In this article, learn the most important TEXT Functions in Excel with 6 everyday examples.

The 6 Important Text Functions

LEFT Function

Use LEFT function to extract portion of text from left.

Examples:

=LEFT(“Chandoo”, 2) will be Ch
=LEFT(A1, 4) will be first 4 letters of A1 value

RIGHT Function

Use RIGHT function to extract portion of text from right.

Examples:

=RIGHT(“Chandoo”, 2) will be oo
=RIGHT(LEFT(A1, 4),2) will be two letters from the middle, starting from 3rd letter of A1.

MID Function

Use MID function to extract portion of text from middle, from a specified starting point.

Examples:

=MID(“Chandoo”, 5, 2) will be do
=MID(“Chandoo”, 4, 99) will be doo

LEN Function

LEN function measures the length of a text in number of characters.

Examples:

=LEN(“Chandoo”) will be 7
=LEN(A1) will be the length of contents in A1. If A1 is empty, this will be 0.

FIND Function

Find the starting position of a text in another text using FIND function.

Examples:

=FIND(“do”, “Chandoo”) will be 5

=FIND(“DO”, “Chandoo”) will be error as find is a case-sensitive function

=SEARCH(“DO”, “Chandoo”) will be 5. 

TEXTJOIN Function

Combine (concatenate) a bunch of values with a specified delimiter.

Examples:

=TEXTJOIN(“,”,FALSE, “Chandoo”,”Jon”,”Mike”) will be Chandoo,Jon,Mike

=TEXTJOIN(” “, TRUE, A1:A10) will combine all non-empty values  in range A1:A10 with space as delimiter.

Learn more about TEXTJOIN function.

The 6 Everyday Examples

Now that you know the 6 important functions, let’s see them applied in 6 everyday situations.

For the purpose of these examples, we will use below sample tabular data & structural references.

1) Gender code (M for male, F for female)

Use the formula =LEFT([@Gender], 1) to get make the gender letter code.

2) Extract first name from name

Use the formula =LEFT([@Name],FIND(” “,[@Name])-1) to get the first name.

FIND gets the position of space, left gets everything before that.

3) Extract last name from name

Try the formula =MID([@Name],FIND(” “,[@Name])+1,99) to get the last name.

FIND gets the position of space, mid  gets everything after that.

4) Print name in Last name, First name format

The formula =MID([@Name],
FIND(” “,[@Name])+1,99)
&”, “&LEFT([@Name],FIND(” “,[@Name])-1)
will convert value in Name column to last name, first name format.

It is a combination of the formulas shown in 2 & 3.

5) Combine all male staff names in to one cell

The formula =TEXTJOIN(“, “,TRUE, IF(staff[Gender]=”Male”, staff[Name],””))
will return all male staff names in the table staff.

The IF formula generates a list of all male names or blanks. TEXTJOIN ignores the blanks (second parameter is TRUE) and combines the values with a comma as separator.

6) Word count of a sentence

Assuming you have sentence in cell D6, the formula

=LEN(D6)-LEN(SUBSTITUTE(D6,” “,””))+1

will tell you its word count. 

The SUBSTITUTE formula removes any spaces (by subbing them with nothing) and LEN is used to measure the length.

Download the sample file

Click here to download the sample file and practice these functions.

Important Text Functions in Excel Video

If you want a video guide that explains these functions in detail, check it out below or find it on my YouTube channel.

Need a text function? Tell me in comments

Are you trying to make formula to get something done with text in Excel? Let me know in comments what you need and I will try to help.

Also, if you have a favorite Excel text formula trick or patterns, share it so we all learn from each other.

The post 6 Essential TEXT functions in Excel with 6 Everyday Examples appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

FREE Live Event on 28th May, 2021 (Friday)

Getting Started with Power Pivot & DAX

15 useful measures

Signup for the Event...

RSVP Now

The button will take you to YouTube. Just click “Set Reminder” on the video.

 

What is in this event?

• Overview of Power Pivot & DAX
• 15 useful measures
• Data model, relationships & star schema
• Example file provided
• Your questions answered

When & where...

This event is on 28th May (Friday), 2021. It is live on YouTube. Refer to below times.

• India: 28 May, 9:30 PM
• London: 28 May, 5 PM
• New York: 28 May, 12 Noon
• Seattle: 28 May, 9 AM
• Wellington, NZ: 29 May, 4 AM

See you all at the event. Don’t forget to sign up for this. It’s going to be powerful.

Got a question? Leave it here.

The post Getting Started with Power Pivot & DAX – FREE Live Masterclass appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

How to create a fully interactive Project Dashboard with Excel – Tutorial

In this article & video series, learn how to create a fully interactive Project Dashboard with Excel, as demonstrated on the right. 

You will learn:

• Gantt charts for project planning
• Progress charts for status visualization
• Upcoming tasks & issues
• Preparing final dashboard

Part 1 - Project Gantt Chart

Gantt chart is a classic way to visualize a project’s plan & current status. That is why it forms the corner stone of any Project Management Dashboard. 

In Part 1 of this tutorial, let’s create an interactive, multi-level gantt chart using Excel. Here is a demo of what we shall create.

Resources for the Gantt Chart

Excel files for practice

Use the below files to practice the concepts.

• Blank data file
• Completed Gantt Chart file

Gantt Chart - Video Tutorial

I made a fun & detailed video on how to create this Gantt chart with Excel. Watch it below or on my YouTube channel.

Getting the data

Any project is a combination of people & tasks. So in order to create a project plan gantt chart, you need both people & project activity data. Here is the data for our Project Mega Something. 

Download the blank data file.

Project Plan Data - Preview

Activities Table

People Table

Set relationship between tables

Once we have the data ready, connect people & activity tables. You can create a relationship from Data ribbon > Relationships.

We want to connect Activities Owner column with People Person column.

Tip

No need to use X or VLOOKUPs anymore, you can connect tables on a column with Excel.

Learn more about Table Relationships in Excel.

Calculations Worksheet

As our tables are related, we can now calculate all the necessary numbers needed for our gantt chart.

In a new worksheet, 

• Insert a pivot table (Excel will create the pivot from your table relationship data model)
• Add Category & activity to row label area
• Add start date and end date to values area
• Set start date “summarize values by” to Min
• For end date, set it to Max
• Set up pivot table in outline format and add sub-totals on top.
• Remove any grand totals.

Your final pivot table should be like below.

Specify Gantt Start date

In a blank cell in this new worksheet, define starting date for our gantt chart visual. You can use a formula like =TODAY()-14 or something else for this.

Name this start date cell as start.date

Gantt Chart Worksheet

Add a new worksheet and name it Gantt Chart.

In this sheet, set up your gantt chart grid. We will use 67 columns in this fashion.

1. Category name
2. Activity
3. Person assigned to the task
4. Start
5. End
6. % done 
7. % done

• 8 to 67 – Dates (narrow columns)

Load up values by either linking Pivot Table values a references or using lookup functions.

Tip

You can use XLOOKUP function to get the person & % done values.

All about XLOOKUP function.

Gantt Chart Formulas

We need to set up a formula in our gantt chart grid to show TRUE when a date in the top row is between start and end dates.

You can use AND formula for this. Fill this formula for the entire range.

In my spreadsheet I had 105 rows x 60 columns.

				
					=AND($F7<=J$3,$G7>=J$3)
				
			

Conditional Formatting Rules

Our gantt chart is nearly ready. We need to add two conditional formatting rules to highlight the project dates & current date.

• Rule #1 : Highlight all TRUE values in the gantt grid
• Rule #2 : Highlight the column that corresponds to TODAY()

Examine the rules from below screenshot.

Finally select the AND formula range and apply custom number format of ;;;

Tip

You can use hide values in a range of cells by applying the custom format code ;;;

How to hide values with number formats.

At this stage, your gantt chart should look like below:

To add % done data bars:

• Select % done column and add data bars.
• Set the bar rule to show bar only
• In the 7th column (which has % done value duplicated), apply a cell icon of when the % done is 100% and no cell icon for rest.
• Hide the contents of this column with custom code ;;;

Tip

Learn a few more tricks about conditional formatting here.

Team selection slicer feature

Go back to the calculations sheet and add a team slicer on your pivot table.

Cut and move this slicer to the gantt chart page.

Your gantt chart is now interactive!!!

Tip

Excel slicers offer a great deal of interactivity in your reports. If you have not used them before, I suggest learning a bit about them. Use this handy guide.

Excel slicers – complete guide.

The final gantt chart...

Here is the final gantt chart at this stage.

Excel files for downloading

Use the below files to practice the concepts.

• Blank data file
• Completed Gantt Chart file

More Gantt Charts for you...

• Gantt Chart with Drill-down feature
• Gantt Box Chart - for showing uncertainty in the project
• Gantt Charts + Other useful PM Templates by Chandoo

Ready to use Project Management Templates

Create awesome project management dashboards and reports using my templates. Trusted by 20,000+ project managers all over the world, these templates are designed to make you look awesome. Create Gantt charts, timelines, time sheets, issue trackers, risk trackers, project dashboards and portfolio dashboards using my templates.

Click here to get them today.

Part 2 - Beautiful Progress Charts

In Part 2 of this tutorial, we will build beautiful project progress charts with Excel. Stay tuned.

Got something to say?

The post How to create a fully interactive Project Dashboard with Excel – Tutorial appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

FREE Live Event on 30th April, 2021 (Friday)

Excel for Project Managers

4 focus areas

Signup for the Event...

RSVP Now

The button will take you to YouTube. Just click “Set Reminder” on the video.

 

What is in this event?

• Practical Excel Techniques for Project Professionals (managers, PMO analysts, leads etc.)
• Gantt charts with Excel
• Trackers for issues, risks etc
• RAG Status visuals
• Project Management Dashboards
• Example file provided
• Your questions answered

When & where...

This event is on 30th April (Friday), 2021. It is live on YouTube. Refer to below times.

• India: 30 April, 9:30 PM
• London: 30 April, 5 PM
• New York: 30 April, 12 Noon
• Seattle: 30 April, 9 AM
• Wellington, NZ: 1 May, 4 AM

See you all at the event. Don’t forget to sign up for this. It’s going to be epic.

Got a question? Leave it here.

The post Excel for Project Managers – 4 Focus Areas – FREE Class on 30th April appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

In this tutorial learn how to,

• Extract tabular data from one PDF to Excel
• Combine and extract tables from multiple PDFS to Excel

 

We will be using Excel 365 & Power Query to do this. If you have different version of Excel (2016, 2013 or older), read the FAQ section at the end for another way to do this.

How to extract PDF table to Excel

Optional:  If you need a sample PDF to practice these concepts, use the randomly made credit card statements I created. Download them from here.

Step 1: Go to Data ribbon & click on Get Data > File > PDF

From data ribbon, use the PDF option  and point to the location on your computer (or web address).

Step 2: Select the table(s) you want in the navigator screen

Power Query will open up a navaigator screen. Just specify the table(s) you want. Refer to below illustration to know more about the navigator screen.

Bonus tip: Use the composite table if you want to get a data table in your PDF that spans multiple pages. This is excellent for bank or credit card statements.

Step 3: Load or Transform data

If the preview in navigator looks satisfactory, just load it. Otherwise, click on “Transform data” to open query editor to make any final adjustments.

Combine & Extract data from multiple PDFs

Step 0: Place all your PDFs in a folder

Step 1: Folder connection

Instead of PDF option, use the Folder option in the Get Data.

 

Step 2: Choose “Combine” in file listing screen

Power Query will show you a screen with a list of all files it found in the folder. Choose any of the combine options here to combine the data from all files to one table.

Step 3: Select the table you want from Transfer Sample Screen

Now, you will see another navigator like screen. Just select the table you want in here. Power Query will go to each file in the folder, get the same table and combine them.

Step 4: Load or Edit the query

And enjoy.

Practice PDF Credit Card Statements

If you need a sample PDF to practice these concepts, use the randomly made credit card statements I created. Download them from here.

Video - Convert PDF to Excel

Still not sure how to extract data tables from PDF to Excel? Watch this short video and get it. See it below or on my YouTube channel.

PDF to Excel - FAQs

I don’t have PDF option in my Excel. What do I do?

You can use free Power BI Desktop to do the same. (Download Power BI for free here)

Once you have Power BI, open it, go to Get Data > PDF and follow the same steps as above tutorial.

Instead of loading the data, copy the entire table from Query Editor and paste it to Excel. See below illustration.

I have new files, how do I refresh?

Just place the files in the same folder.

Go to Excel and right click on the extracted table and select “Refresh”. Excel will update the details.

I want to exclude certain files in the folder when combining…

Open the query editor and go to the query that is responsible for your combining PDF process. Go to source step. This will show all the files in the folder. 

Include a filter condition here. Power Query will warn about inserting a step. Proceed and you will be able to exclude files based on conditions.

Examples:

• Process files that have file name starting with certain letters
• Files created after certain date
• Having specific extension.

Remember: Power Query is case sensitive.  

I want to pre-process or clean-up data before loading it into Excel

Open the query editor and add any necessary data transformation steps at the end. 

Examples:

• Removing all foreign currency transactions from credit card statements
• Cleaning up account codes
• Rearranging columns in the PDF data table

For more on what you can do with Power Query, check out this tutorial.

Other questions…

Post a comment and I will try to help you.

More examples on Power Query…

• Introduction & 4 full examples of Power Query
• Combine files with Power Query
• Extract common values between two tables
• 5 Power Query tips for accounting & finance people

The post Extract data from PDF to Excel – Step by Step Tutorial appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

				
					=IF(XOR(D8="Male",G8<85000),"Include", "Exclude")
				
			

				
					=IF((E8="Website")+(YEAR(F8)=2019)+(G8>90000)>=2,
        "Include", "Exclude")
				
			

				
					=IF(MEDIAN(F8,DATE(2019,1,1),DATE(2019,6,30))=F8,
        "Review","")
				
			

				
					=IFS(E8="Website",1%,
        AND(E8="Sales",YEAR(F8)=2018),3%,
        TRUE,2%)
				
			

				
					=2% - (E8="Website")*1% + AND(E8="Sales",YEAR(F8)=2018)*1%
				
			

				
					=IF(COUNTIFS($C$32:$C$36,C8),"On call","Not on call")
				
			

				
					=MEDIAN(IF(E8:E23="Website",G8:G23))
				
			

				
					=TEXTJOIN(",",,IF(E8:E23="Finance",C8:C23,""))
				
			

				
					=IF(COUNTIFS(C8,"*bo*"),"bo person","not a bo person")
				
			

				
					=AND($E8=$J$50,$D8=$J$51)
				
			

				
					=IF(G8>AVERAGE($G$8:$G$23),G8,NA())
				
			

The post 10 Advanced IF formula tricks you must know appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

The post Advanced Data Viz with Excel – Live Event on 26 March appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Time for another homework. You got a spreadsheet of department budgets and need to write a formula to get budget for a given department, month combination.

Homework Specifics:

Assume your data is in the table named budgets, D18 has department and D19 has month values.

You can use any formula in Excel to get the answer. Let’s get creative and have fun.

Post your solutions in the comments section. GO!!!

Need a sample file? Get it from here.

Need a hint? Check out my 2D lookups article.

Want more homework problems? See this page.

The post Write a formula to get Department Budget for a Month [Homework] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

The post How to embed Excel files, calculators on your website? – Step by step instructions appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Live event on Friday, 26 Feb – 2021

Dynamic Array Functions

Folks… Come and join me for a live event on Excel Dynamic Array functions this Friday (26 Feb – 2021). We are going to cover,

• Why you should care about Excel DA functions?
• The incredible # operator
• 6 new functions in Excel - FILTER, SORT, SORTBY, UNIQUE, SEQUENCE & RANDARRAY
• 25 real world examples of DA formulas
• Your questions answered

RSVP here

Fine Print & FAQs

• This event is live on YouTube. Click on the button to see the time in your timezone
• The live event will be 60 minutes long (possibly longer)
• This is a FREE event. Bring a cup of coffee or beer, your computer and enjoy.
• NO RECORDING!!! Join live or wait for next one. I won't be publishing recorded version of this.
• Full example file is provided for you to download and practice along.

So what are you waiting for? Click here and RSVP.

The post Join me this Friday to master Dynamic Array Formulas appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

The post 5 Simple & Useful Conditional Formatting Tricks appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Hello readers and fans…

On behalf of my family, our staff, volunteers & well-wishers, Let me wish you a very happy Christmas & prosperous new year 2021. Here is a small holiday card from Chandoo.org HQ. The kids school had a cultural festival a month ago and we got this beautiful picture at a photo booth. So much better than timer controlled DSLR on our dining table, I must say.

Thank you

2021 has been a strange year. I know many of you would have faced some form of hardship this year. So I won’t bore you with my details. Instead, let me be thankful for what it has been. Big shoutout to…

• You – my dear reader, viewer and supporter of Chandoo.org for being with me all this time and learning.
• My family – for supporting & loving me all these years
• Microsoft – for creating Excel & Power BI
• YouTube – for providing an awesome platform to share my knowledge with you
• My teachers – too many to name, but they have always guided & inspired me
• My partners – for helping my business and customers – big shout out to Plum Solutions & Global Excel Summit
• My staff – Ravindra, Pothi & Chitti for being a part of our team and running our small kingdom smoothly
• Our forum members – for lending hand to those who need help
• My customers – more than 25,000 of them. Thank you for trusting me.
• Blog friends – for sharing ideas and referring people to Chandoo.org
• Software providers – for helping me run Chandoo.org – big shout out to WordPress, Elementor, Stripe, E-Junkie, WishlistMember, KnownHost, ConvertKit, Amazon, Gmail and Techsmith

This holidays…

I feel incredibly fortunate to be living in New Zealand where covid is under control. This allows us to spend a bit of time outside our house during the holiday season. We are going a little road trip to Coromandel peninsula on the north-east side of NZ. I will share a post card from the beach when we get there by New Year.

Stay safe and see you all next year with awesome Excel & Power BI goodness. Enjoy your holidays.

The post Merry Christmas & Happy New Year 2021 appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Do you ever want to have an image or picture lookup in Excel? Something like below.

In this article, learn how to set up an image lookup using Excel. You can use this to display staff details, product images or machine parts etc.

Video - Dynamic Image Lookups in Excel

If you want to understand the picture lookup technique and see it in action, check out my YouTube video below. For text instructions and images, please read on.

Step 1: Set up your data

To get image lookups, you need to set up your data, ideally in a separate worksheet. The trick is to have one image per cell. Something like this:

Tip: Do not use Tables for storing your data. Instead keep them in a spreadsheet range.

Your images need to fit snugly inside the cell, without touching the boundaries for best results.

Also make sure to turn off gridlines on that spreadsheet tab.

You can do this from View ribbon, as depicted below.

Step 2: Write formula to lookup the image

In a separate worksheet, we will write formula to lookup images. Let’s say we have an input cell with employee name in C5.

We need to get the matching employee picture.

The trick is to write a formula that returns a reference to the image of employee in C5.

You can use either INDEX+MATCH or XLOOKUP. 

Let’s use XLOOKUP:

=XLOOKUP(C5, data!$D$3:$D$14, data!$C$3:$C$14) 

This will return a reference to the cell with the image of employee in C5.

Of course the result of the formula would be 0, as column C (data!$C$3:$C$14) has no values (just pictures) in it.

Step 3: Insert a named range with the formula

Now that we know what formula to write, go to Formula ribbon and click on Define name button. 

• Name will be “employee_picture”
• Refers to will be our XLOOKUP formula from above

See below illustration to understand how the name can be created.

Step 4: Set up a picture link to show image lookup result

This is the last step folks. Go to the data worksheet where you have all the pictures.

1. Select any one cell with image and copy it (CTRL+C).
2. Go back to the results worksheet, right click anywhere and select linked picture from Paste Special options. See below picture to understand.

 

3. You will get a picture of the selected employee. But this one is special. It is linked to the cell you copied. 
4. Select the picture and formula bar will show the address of the cell to which it refers.
5. Now comes the most important step. Go to formula bar and edit the reference. Type =employee_picture and press enter.

Bingo, you have just created a picture lookup. 

If you change the input cell and type a different person name, the lookup image will show that person.

Related: Learn more about Picture Links

Tips on using Image Lookup Technique

Image lookup through picture links offers exciting possibilities. Here are some tips & gotchas you want to keep in mind.

• The picture link images are just like any other images in Excel. So when you select them, you can use picture format ribbon to apply various formatting options to them. For example, you can crop them to shape or apply shadow effect to them.
• Picture links work best if all images are of same size. 
• While picture links look great on screen, they tend to be grainy when printed.
• They work well with dashboards too. For example, You can use picture links to display top 3 products or bottom 5 sales persons in dashboards.
• For image lookup scenarios, consider adding a missing person image and use that in XLOOKUP if_not_found parameter. It looks amazing.

Download Picture Lookup Example File

Click here to download sample file with the picture lookup example. It has few different XLOOKUP formulas, an additional image lookup trick to show team members and more. Examine the named ranges to learn how it works.

Got questions about image lookups? Fire away...

Give picture lookups a try and if you face any issues during implementation, please post a comment here so I can help you.

Also, check out these other types of lookups:

• Range lookup, find matching range based on input value
• Lenient lookup – get the almost matching value
• Pricing tier lookup problem
• Important lookup functions in Excel
  • XLOOKUP
  • VLOOKUP
  • INDEX+MATCH
  • OFFSET

The post Image Lookup – How-to show dynamic picture in a cell [Excel Trick] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

I learned about a new HR technique for talent mapping during a recent consulting project . It is called 9 box grid. In this page, let me explain what it is and how you can create 9 box grid using Excel.

What is 9 Box Grid?

9 Box grid is a talent mapping tool for Human Resources. This is used to map your staff on two scales – performance and potential. All staff are plotted on 3×3 grid with one side showing performance (from low to high) and another side with potential (low to high). 

Here is a sample 9 Box Grid.

Learn more about 9 box grid methodology here [analyticshr.com]

How to create 9 box grid in Excel?

If you have just a handful of staff then it is easier to create 9 box grid by typing the data in 3×3 range in Excel. But if you have a lot of people, then creating a dynamic 9 box grid is helpful. Something like below:

Interactive 9 Box Grid in Excel - Demo

Step by step instructions - Making 9 Box Grid template in Excel

I made a short video explaining the process for creating 9 box grid for talent mapping in excel. Watch it below (or on my YouTube channel) or read on for text instructions.

Step 1: Gather data about performance and potential of your staff

In a blank spreadsheet, gather the data about your staff. At the very least, you want staff name, performance and potential. Feel free to add things like department or team names.

This is how I structured my data:

It is all in a table named talent. 

Step 2: Create the talent map 9 box grid visual

You can do it in two ways. If you have Excel 365 and access to dynamic array functions like FILTER(), you can use formulas to generate the 3×3 talent map grid.

Else, you can use Pivot Tables to generate the talent map grid.

Formula approach for Talent Map

Note: this method requires Excel 365 with dynamic array functions.

Set up a 3×3 grid in a blank sheet. 

In the top left cell, use the below formula.

=IFERROR(TEXTJOIN(“,”&CHAR(10),TRUE, SORT(FILTER(talent[Candidate],(talent[Performance]=E$8)*(talent[Potential]=$D5)))),””)

The above formula assumes E$8 has the performance rating value and $D5 has potential.

How does this formula work?

We are using FILTER() function to filter down the talent table to all candidates who have given performance (E8) and potential (D5) values

SORT() function sorts these names in alphabetical order.

Finally TEXTJOIN() is used to combine all names to one big text with comma & new line character between names. 

• Learn more about FILTER & SORT functions.
• Learn more about TEXTJOIN function

Pivot Table approach for Talent Map

Note: this method requires Excel 2013 or above versions

Here is a quick demo of the Pivot Table based 9 box talent map grid.

To create this,

• Insert a pivot table from your talent data. During the insert pivot step, enable “add this to data model” option, as depicted below.

Read more about data model based pivot tables.

• Now add “performance” to column labels and “potential” to row labels area of the pivot.
• We need to show the names of people instead of count in the values area. This can be done by using measures. 
• Right click on the table in fields list area and select “Add measure” option.
  • Name of the measure: List of names
  • Definition: =CONCATENATEX(‘talent mapping’, [Candidate],”, “,[Candidate],asc)

Add this measure to values area of the pivot and your 9 box talent map will be ready.

What is this measure doing?

This measure is concatenating all the staff names that have given performance and potential levels. It is also sorting such names in ascending order. 

9 box grid for talent mapping - FREE Excel Template

Click here to download my free 9 box grid talent mapping template.  It has sample data of 100 employees and two different talent maps (one with formulas and another with pivot tables). Experiment with it to understand the process or use it for your work.

Work in HR and use Excel? More resources for you...

Here are a few more resources to help you with various HR data analysis and reporting challenges.

• Impressive vacation tracker and dashboard
• L & D tracker and dashboard
• Employee turnover (attrition) analysis with Power BI

General Excel resources to improve your data analysis

• Top 10 formulas for data analysis work 
• Excel pivot tables – a complete intro and examples
• Advanced Pivot Table examples

The post 9 Box grid for talent mapping – HR for Excel – Template & Explanation appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Line charts are essential for trend analysis, comparison over time, spotting distribution or volatility in your data. In this page, let me introduce 6 powerful line chart variations to help you with business data analysis. These are,

• Indexed Line Chart
• Sparklines
• Spaghetti Line Chart
• Smoothed Line Chart
• Forecast Line Chart
• Line Chart Combinations

Line Chart Variations - Video

In the below video, I explain 6 line chart variations with instructions on how to create and work with them. Watch it or read on for the chart details.

Indexed Line Chart

Indexed charts are great for comparing apples with oranges. For example, you can compare sales of two products over a period of time with Indexed Chart, even if the sales numbers are in different ballparks (one sells in millions, other in thousands). 

Here is a demo of indexed line chart.

Learn more:

Click here for a tutorial on how to create indexed charts in Excel.

Sparklines

Sparklines are tiny charts that you can fit in a cell. They pack a lot of information and help in visualizing more items. 

Here is a sparkline demo:

Learn more:

Understand how to create and work with sparklines.

Spaghetti Line Chart

Often we have too many lines and that can be distracting. Use a spaghetti line chart to drive focus to one of the lines. You can make them interactive so users can pick the noodle they want to taste. 

Here is a demo of interactive spaghetti chart.

Learn more:

Watch the line chart variations video [timestamp 3:48]

Smoothed Line Chart

Sometimes our data is too volatile. In such cases, you can smooth it to spot the trend and meaningful changes. 

There are various ways to smooth line charts. You can use “Smooth line” option of the chart or apply moving average based smoothing.

Here is a demo of both techniques.

Learn more:

How to calculate moving average in Excel?

Advanced: Smoothed area charts – xmen graphic poster

Forecast Line Chart

Use Excel’s FORECAST.ETS() and other suite of functions to create forecasts from your data and visualize them. You can analyze trend and seasonality of the data with these functions. Forecast charts are helpful in business reporting.

Here is a demo of a forecast line chart.

Learn more:

Introduction to Excel forecasting feature

Line Chart Combinations

No matter how hard you try, sometime a line alone won’t do. In those cases, you can easily combine line charts with other visualizations in Excel. Combination Charts are very easy to create. 

Here is a demo of line chart with column combination.

How to create combination charts?

• In new versions of Excel (365, 2016+), just go to Insert ribbon and click on Combo chart button (the one that looks like a line column combination).
• In earlier versions of Excel, just make a line chart, right click on the line that you want to change to something else and select “Change  series chart type” option.

Don't forget the labels ...

You can make regular or any of the variations listed above better by adding sensible labels and chart titles that tell a story. 

Here is a demo of special labels on line charts.

Learn more:

• Descriptive and useful titles on charts
• Chart legends that are legendary
• Highlighting important data points on a chart

Download Line Chart Variations Workbook

Click here to download the Excel file with all these variations. Play with the data or charts to learn more about these 6 line charts.

If you have any questions or suggestions, post them in the comments section.

The post 6 Must Know Line Chart variations for Data Analysis appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Here is a super easy and fun way to create two-level data validation list within Excel. Something like this? In this article, learn how to easily create such two or multi-level drop down lists using Excel.

Two-level data validation drop down - video

If you want a video with all these steps (and some bonus tricks), check it out below. For text explanation with images, just read on.

Download Sample File - Two-level Drop Downs

Click here to download the sample file used in this example. Play with the data or formulas to learn more.

Step 1: Set up your source data

You need data in this format for this two-level drop down technique.

Step 2: Make a pivot table in outline layout

Insert a pivot table from your validation data in outline format with level 1 and level 2 in row labels area. Turn off any grand totals and sub-totals. You will get this.

Related: Introduction to Excel Pivot Tables

Step 3: Formulas to get the two-level structure

Now, just refer to pivot table items to generate two-level structure.

We need two formulas. Both are simple to write.

The first one is IF formula

The logic is simple.

• If state column is not blank, get that value
• Else, if City is also not blank, prefix 5 spaces to city value and return that.
• Else return FALSE

The below formula works for me.

=IF(J27<>””,J27,IF(K27<>””,REPT(” “,5)&K27))

My Pivot table is from J27.

Drag this formula down for 50 rows or whatever is the size of your data.

Filter formula to exclude all FALSE values

This next formula will just remove any FALSE values in our 50 cell range. We can use FILTER for this.

=FILTER($O$27:$O$76,$O$27:$O$76<>FALSE)

Note: My IF formulas are in the range O27:O76.

Here is an illustration explaining both of these formulas.

Related: Filter & other Dynamic Array functions new to Excel 

Step 4: Set up the validation rule

We are done people… Just select the cell where you want the two-level data validation drop down to appear and use Data ribbon > validation button. 

Set up the validation type as “list” and use the spill range as list. For example, in my workbook, the filter formula is in cell P27, so DV list is $P$27#

Cherry on top: Conditional formatting to flag errors

Our two level validation is already yummy, but if you want a cherry on top, use conditional formatting to flag errors when user picks “level 1” item instead of “level 2” item.

Something like this:

How to set up the conditional formatting rule for this:

• In the adjacent cell to the validation cell, write an IF formula that would return or with a message.
• Color the cell in Red.
• Now apply conditional formatting rule on the cell to turn the color to GREEN if it is

Related: Introduction to Excel Conditional Formatting

Whipped cream on top: Box drawing shapes + validation

Instead of 5 spaces, you can use box drawing shapes like ∟⊢ to create an impress tree structure. Something like this:

Download Sample File - Two-level Drop Downs

Click here to download the sample file used in this example. Play with the data or formulas to learn more.

More tips on data validation

If you are looking for ways to spice your data validation, you’ve come to right place.

• Create awesome data entry forms with validation + conditional formatting
• Mixing lookups with validation
• Make cascading drop downs in Excel
• Remove duplicates in data validation list

The post Two-level Data Validation [Excel Trick] appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Ever have data in a calendar format and just wished you can get it in to tabular format? Something like this:

You can use Excel formulas or Power Query to do this. In this article, let’s review formula based approach with two excellent choices.

Calendar to Table Formulas – Video

If you want to understand the formulas, watch below video or read on.

Using SUMIFS formula

We can use the SUMIFS formula to easily get data for any date, as it works with 2D ranges too.

Let’s say our calendar data is in the range B5:H16,

Use the formula =SUMIFS(B6:H16, B5:H15, “1-Aug-2020”) to get value corresponding to 1-Aug-2020.

Notice the difference between Sum range and Criteria range.

We move the sum range one row down so that we can look at dates above and get the corresponding value from below.

Limitations of SUMIFS approach:

• Works only for numbers. If you have text values against each date, then this method will not work.
• Can be wrong. Let’s say one of the values is 44044. Which is the number representation of 1-Aug-2020 (as Excel dates are just numbers. More on working with date & time here) Then our SUMIFS result will be wrong.

Learn more about SUMIFS formula.

Using INDEX formula

We can use INDEX formula to access any item in a range by specifying row & column numbers.

For example, =INDEX(A1:D10, 3,2) will return the value in 3rd row & 2nd column of A1:D10, ie B3 value.

So if we can calculate the row & column co-ordinates for a given date from our calendar, we can easily get the answer.

Column number: As our data is in a calendar format, each date will only fall in the column corresponding to the weekday number. We can use =WEEKDAY(input_date, 2) to get the column number, given that our calendar is from Monday to Sunday.

Row number: Once we know the column number, we can extract the entire column and look for given date in that column with MATCH function. This will give us the row number too. The below formula works fine.

=MATCH(input_date, INDEX(calendar_data, , WEEKDAY(input_date, 2)), 0)

Note: the internal INDEX() formula returns entire column as we omitted row parameter. Read up more on this functionality of INDEX formula.

Final formula:

Here is our final formula (with calendar in range B5:H16)

=INDEX($B$5:$H$16,
   MATCH(input_date,INDEX($B$5:$H$16,,WEEKDAY(input_date ,2)),0)+1,
   WEEKDAY(input_date, 2))

Related: Learn more about INDEX formula.

Download Example Workbook

Please click here to download the example file with calendar data to table format formulas. Examine the formulas or write your own to understand this technique.

Other ways to convert calendar format data

• You can use Power Query to do this job. I highly recommend using PQ, if you deal with calendar style data often. Here is a primer on Power Query.
• I have previously written about this problem (with SUMIFS solution) and asked my readers to share their formulas. You can see many other interesting & creative solutions here
• VBA: You can use a simple macro to quickly reshape the data. This is a preferred option only if you can’t use Power Query. Here is a tutorial with such macro. The comments on this link also feature many Power Query based transformations.

How would you deal with calendar style data?

For something quick and easy, I will use formulas. For an on-going situation, I will use Power Query.

What about you? How would you deal with calendar style datasets? Please share your thoughts in the comments section.

The post Excel formula to convert calendar format to table appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Power Query (Get & Transform data in Excel) is a true game changer. It can simplify and automate various data activities. The key benefits of Power Query are,

• Connect to any type of data source and fetch data
• Define process for data cleansing and transformation
• Combine datasets (table joins, appends)
• Automate data collection & management tasks (refresh etc.)

In this Power Query Tutorial, learn what it does, how to use it with 4 practical examples, tips & tricks to work with Power Query better.

Table of Contents

What is Power Query?

Power Query is a feature of Microsoft Excel and Power BI programs. Power Query is used to,

• Set up connections to various data sources
• Pre-process data – ex: cleanup, adding columns, filtering, sorting, mashing up
• Publish the finalized data to source system – Excel or Power BI

Think of Power Query as your own SQL (querying) tool built in to Excel (or Power BI). Use it to manipulate, mash-up and manage your data.

How does Power Query look?

Power Query window looks almost like Excel workbook. This is why most beginners get confused. The key thing to remember is, Power Query is for connecting, cleaning and manipulating data. You do this by applying steps on your data.

 

Here is an example of Power Query editor window with 6 key areas highlighted. Click on the image to expand.

Power Query Window - 6 Important Areas

1. Ribbon: You will see the ribbons on top of Power Query editor UI. There is home ribbon where common query options like column / row adjustments, clean-up and joins are found. The other ribbons are transform, add column and view.
2. Queries Pane: This is where all the queries (or connections) you have in the workbook are listed. You can select a query to preview its results and work on it.
3. Data view: You find the preview of current step of selected query here. As you make changes to the data, this preview is updated. This data view looks almost like Excel spreadsheet view.
4. Query settings: You can adjust query name, see all the applied steps here. We use this to make changes to the query or delete any steps.
5. Formula bar (optional): The optional formula bar shows corresponding M Language code for current step. You can enable / disable this formula bar from “View ribbon”.
6. Close & Load button: This button, also shown as “Publish” in Power BI is what we use to close Power Query and return to main application (Excel or Power BI).

How to activate Power Query?

The process for activating or launching Power Query is slightly different in Excel vs. Power BI. Refer to below steps to launch Power Query.

Launching Power Query from Excel

1. Goto Data ribbon in Excel
2. Use buttons in “Get & transform data” area to make a new connection.
3. Or click on buttons in “Queries & Connections” area to refresh or view the existing queries.

Power Query from Power BI

1. Goto Home ribbon in Power BI
2. Use buttons in “data” area to make a new connection.
3. Or click on buttons in “Queries” area to refresh or view the existing queries.

Notes on Power Query availability

• Excel: Power Query is available in all versions of Excel from 2013.
• Power BI: Power Query is available in all versions of Power BI.

Power Query Tutorial - Video

I made a 1+hour tutorial on Power Query explaining every aspect of it, along with 4 full examples. Please watch it to understand and master this powerful & time-saving technology.

You can watch it below or head over to my YouTube channel.

Power Query as a Mind-map

Power Query is packed with thousands of features to clean, process and manage data. That is why it is tricky to comprehend the overall picture of it. I made a mind-map of Power Query so you can get holistic view of this life saving tool. See it below (click on the image to enlarge).

How to use Power Query?

---

Four Examples

In this section, I will demonstrate how to use Power Query with four full-length examples. Each example has sample data and completed workbook for you download and follow along.

Download Example Files

Includes sample data, completed Excel workbook & Power Query Mind-map PDF

Power Query Example 1:

---

Load and Clean-up Employee Data

In the first Power Query example, we will look at data for one thousand staff and clean it up. The data is in an Excel file. We will load it to Power Query and perform below clean-up activities. After data is clean, we will publish the dataset to Excel for analysis. 

• Connect to Employee Data file
• Replace missing gender & department values
• Remove employees without salary
• Extract employee’s country and remove address column
• Extract year of join
• Publish data to Excel

Whenever there are new employees, we will simply refresh the Power Query connection and it will load new data (after apply clean-up steps) automatically. Just like magic.

The below instructions show how to do this with Excel Power Query. You can apply the same steps in Power BI too.

Step 1: Connect to the data set file from Excel

Sample file for this example - M01.xlsx You need the employee data sample file - M01.xlsx for this example. In the downloaded ZIP file, you will find it in "Datasets" folder. × Dismiss alert

• Go to data ribbon and click on “From File” and select “Excel”. Point to the employee data set.

Here is a quick re-cap of how to connect to data from Power Query.

Step 2: Apply data cleansing steps in Power Query

Once the data is loaded into Power Query, you can quickly apply all the necessary data cleansing steps in there. 

The steps will be:

1. Remove top rows: The file contains 2 rows of header information which is not needed. From “Home ribbon” in Power Query editor, using the “Remove rows” button, remove top 2 rows.
2. Promote headers: Now that our data is has no extra rows on top, let’s use row number 3 as header. From home ribbon, just tap on the “use first row as headers” button.
3. Remove blank columns: The file also loads a few blank columns. Just use “Remove columns” button to nix them.
4. Replace missing values – gender & department columns: Select each column with missing data and either right click or use “replace values” button to find & replace nulls with alternative values.
5. Remove staff with no salary: This operation is also called “Filtering”. Just use the filter button on salary column and remove any “null” values. 

I have illustrated the screen buttons for these 5 data cleansing steps on Power Query UI below. Check it out if you need help.

Power Query Tip: Plan first, clean next

Save a lot of time by planning all your data cleansing steps first. Think about ways in which your data could change in future and build your data cleansing around them.

Step 3: Extracting country from address to new column

So far, all our data cleaning steps are in-place. But now, we must add a new column with the country of employee. You can use “Add column” ribbon of Power Query to do such operations.

For example, to extract “USA” from the address “1 Infinite Loop, Los Angels, CA, USA“, we can use text after delimiter option. 

To extract the country, select the address column and use Add Column > Extract > Text After Delimiter option.

Note: In the video, I use a more advanced version of this as our addresses are not so straight forward.

You can use similar approach to add “year” from date of join.

Step 4: Publish data to Excel for analysis & reporting

Once your data is clean and ready, click on “Close & load” button in home ribbon. This will load data to Excel as a table. You can use this table for data analysis or reports. 

How to refresh:

Whenever there is new data added to the employee data file, just head over to the Excel file with connection and refresh it (shortcut: Ctrl+Alt+F5 will refresh all connections)

Your file, associated analysis and charts will all be updated.

Power Query Save & Load options – Explained

• In Excel: You can load Power Query data in three ways – as a table on spreadsheet, a table to data model or connection only.
• Power BI Save & Load: With in Power BI, you can either load a Power Query table or leave it in the query editor. If you do not load a table, you can still have it refreshable for calculation purposes.

for more on this example…

Please refer to the Power Query tutorial video above, timestamp 24:10 onwards.

Power Query Example 2:

---

Scraping Web Data & Reshaping it

In this example, let’s use Power Query to scrape web data from List of Indian States page on Wikipedia.

On that page, there is a historical census data table (depicted below) and we will connect to it from Power Query. Once we have the data, we will unpivot it to tabular format for easy analysis.

Data for this example - Wikipedia Please use the URL - https://en.wikipedia.org/wiki/List_of_states_in_India_by_past_population for this example. × Dismiss alert

Step 1: Connect to web data source

From Data ribbon, use the “from web” button. Paste below URL https://en.wikipedia.org/wiki/List_of_states_in_India_by_past_population

and connect.

Power Query will show all the tables on the web page. Select the census table and click on “transform” button.

Step 2: removing unnecessary rows

The wikipedia table has an extra header row and a grand total row. Just remove these with “Remove Rows” button.

Step 3: Unpivoting the data

The census data has state in one column and each census population in one column. We can turn this in to a standard three column table – state, year, population using unpivot option.

• Right click on state column 
• Pick “unpivot other columns” option
• Done, your data is now in tabular format.

 

Power Query Web Connection Tips

• Privacy settings: When you make a web connection, PQ will prompt you for access type details. Most web data can be accessed anonymously. But you can also use login access or windows credentials to authenticate requests.
• Check frequently: If the source website changes their format or presentation of data, then your Power Query connections will break. It is a good idea to check such connections once in a while to make-sure they are working.
• URL parameters: You can use “Advanced” option during connection time to set up URL parameters or variables. This gives you flexibility to access things like page 5 of a result set.

for more on this example…

Please refer to the Power Query tutorial video above – timestamp 47:09 onwards

More Power Query Web Scraping Examples 

• FIFA worldcup – scores, tables & details – web scraping with Power Query [Beginner]
• URL parameter example – weather data to Excel [Beginner]
• Using API to access and export video comments from YouTube with Power Query [Advanced]

Power Query Example 3:

---

Combine data from all files in a folder

You can use “folder” connection option in Power Query to easily consolidate data from all files in a folder. 

In the third example, we will take all the project files in the example dataset and combine data to one table. This process is a bit clumsy to explain in text alone. So please watch the video segment (timestamp 58:48) to understand this fully. I am providing a summary of the folder combine technique below.

Data for this example - CSV folder In the download ZIP file, use the Datasets > CSV folder as data source for this example. × Dismiss alert

1. Start by making a folder connection (Data ribbon > Get Data > From File > From Folder)
2. Point to the folder where your files reside
3. Click on “Combine” button for a simple and quick data combine.
4. Or choose “Combine & Transform” option for customizing the transformation process.
5. Power Query will show one of the files and asks you how you want to extract data. Based on your selection, PQ will apply the same logic to all files and combines the data for you.

Here is a quick overview of the folder connection process.

for more on this example…

Please refer to the Power Query tutorial video above – timestamp 1:09:08 onwards

Power Query Example 4:

---

Joining and Appending Tables

In the last example of Power Query, we will learn how to merge and append tables. These are similar to SQL operations.

• Power Query Merge = SQL Join
• Append = SQL Union

Data for this example - Students dataset In the download ZIP file, use the "students & courses.xlsx" file for this example. × Dismiss alert

Appending two tables with Power Query:

To append one table at the end of another table, you must have same columns in both tables. It doesn’t matter if the columns are not in the same order (for ex. table 1 can have student, course and table 2 can have course, student. PQ will append ok).

What if one table has more or less columns?

In that case, Power Query will still merge, by including all columns. The missing values will be shown as null in the final appended table.

To append two tables:

• Select either table and view in the preview grid.
• Go to Home ribbon > click on “append queries“
• Follow the screen prompts and specify the second table name.
• Power Query will append second table at the end of first table.

You can also use this to append more than two tables.

Merging (Joining) two tables:

In order to merge or join two tables, you nee a common value in both tables. In the below example, you can merge:

• Students & Enrollment on “Student ID“
• Course & Enrollment on “Course ID“

Example of common field between tables

Students Table

• Student ID
• Name
• Date of birth
• Class
• Parent 1 name
• Parent 2 name

Course Table

• Course ID
• Course name
• Instructor
• Credits

Enrollment Table

• Course ID
• Student ID

To merge two tables:

• Select the source table
• Go to Home ribbon and click on “Merge queries“
• Follow screen prompts and select common column (field) between both source and target tables.
• Specify the join type. Leave the default “left outer join” if you just want matching values in target table when present. Try other kinds of join (merge) if you need.
• Power Query will perform the merge and show corresponding rows of target table as a new column.
• Expand this column and your merge will be complete.

Download Power Query Examples

Please use below button to download all Power Query examples, sample data and mind-map PDF. 

Download Example Files

Includes sample data, completed Excel workbook & Power Query Mind-map PDF

Power Query - My Top 5 Tips

Tip #1

Tip #2

Tip #3

Tip #4

Tip #5

More..?

Tip #1

Look before leap:

Before you create any complex queries, just jot down all the steps needed and arrange them in logical order. Apply filters & error handling steps first, then add columns or transformations.

Tip #2

Profile your columns

Power Query offers easy and useful column profiling options. Use them to investigate how good your data is and fix problems with a click.

Tip #3

Formula bar is your friend

Enable formula bar from “View” ribbon. This is a one time step. Once you have it on, you can passively learn the Power Query language – M. You can use formula bar to quickly adjust or type new M code.

Tip #4

Right click before you look elsewhere

Many common and useful Power Query operations can be done by right clicking on the columns. This saves time too.

Tip #5

There is more than one way to do things

and that is ok. Just stick with what works in the beginning. Learn and improve your queries over time.

More..?

Got a Power Query tip? Share it with me in the comments section.

Power Query - Recommended Resources

Power Query Books

I recommend the excellent Collect, combine and transform data using Power Query book by Gil Raviv.

Power Query Courses

If you want a comprehensive and hands-on data analysis course with plenty of Power Query examples, check out my Excel School online classes.

Power Query Web Sites

These are my absolute favorite helpful websites that frequently write about Power Query. Please bookmark and enjoy.

• Excel Guru blog by Ken Puls – For Power Query tips, practical scenarios and guidance.
• Cross Join blog > Power Query pages by Chris Webb – for intermediate to advanced PQ applications.
• M language primer (multi-part series) for advanced users by Ben Gribado
• Power Query best practices [Microsoft Docs]

More PQ examples on Chandoo.org

Check out below examples for more on Power Query.

• Leave analysis with Power Query
• Folder consolidation – advanced example
• Oddly shaped data to table – with Power Query
• More on Power Query

The post Power Query Tutorial – What is it, How to use, Full examples, Tips & Tricks appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Wed, 26 Aug 2020 11:49:17 +0200 https://api.follow.it/track-rss-story-click/v3/r1uM6AV0tArU4K6_FIM7CQ