Recently I had to create a Pivot report from monthly data. But there is a twist. The data is spread across multiple sheets, one for each month. Let me explain how I built the pivot for that scenario.
Step 1: Load Monthly Data File to Power Query
We can use Power Query and automatically combine all the individual sheets to one big table. For this, you need a monthly workbook that has one tab (sheet) per month. If you need sample data, check out this file.
- Create a “NEW” Excel workbook. We will use this file to combine all the monthly worksheets.
- Go to Data Ribbon and click on Get Data > From File > From Excel
- Select your monthly workbook with all the individual sheets
- In the navigator screen, select any one sheet and click on “Transform” to go to Power Query Editor.
Step 2: Combine & Clean-up Data
Once you are in Power Query Editor (PQE), we can quickly combine all monthly sheets and clean-up the data.
- Using the “Query Settings” panel on the right, delete all the steps except “Source” step. You can use the ❌ mark next to the step to delete the step.
- In the query view, expand the “Data” column so we can get all the monthly data in one big table.
But the data is not clean...!
When you “Expand” the data column, we get all the data, but a ton of problems too. Here is a summary of the key issues.
- Incorrect headers
- Repeated header rows (one per month)
- Incorrect data types (dates, numbers treated as Alpha-numeric by Power Query)
- Unwanted columns
- Null values or blank rows
- Something else?!? 😱
Fortunately, Power Query can solve all these problems quite easily.
- Set first row as headers using Home Ribbon > First Row as Headers option
- To fix nulls & repeated header rows, use filter on a column like “Sales Person” and filter away the null & title words. See this demo:
- Right click on the columns and use “Change Type” to set the correct data type.
- Using Home ribbon > Choose Columns select the columns you want.
Step 3: Load the Data to Excel
Once all the clean-up is done, rename your query to something like “Combined data” and use the Close & Load button in Home ribbon to load this data to Excel.
Pro Tip
Use the "Load to" options to load the data just to your data model instead of Excel grid. This is helpful if you just want to make pivots and don't need to "see" the data.
Step 4: Create Pivot Table(s)
Now that all the data is in one place, you can create a pivot report easily in Excel. Just select the “combined data” table and use Insert > Pivot Table to add the reports you want.
Step 5: How to update when you have "NEW" data?
Come next month, you may have another sheet in the “monthly file”. Just save the file and open the consolidated workbook (with all your pivots) and use the “Data” ribbon > Refresh All button to refresh the queries. You may need to press this twice to update the pivots too.
VIDEO: How to create a pivot table from multiple sheets of data?
Check out this video tutorial to understand how to make a pivot table from multiple sheets of data. (see it on my channel)
📂 Sample File
Download the sample data file (monthly data) to practice this concept on your computer.
What to do when you have an error? ⛔
While this trick works most of the time, you can still get some errors. Use below tips to debug / fix the problems.
- Save & Close the Monthly file: The refresh / update process may throw errors if your file is not closed. So, save and close the workbook.
- Monthly tabs should be consistently formatted: The headers and number of columns in your monthly file should match across tabs. If you have different headers or number of columns, then the combined file may have errors or incorrect values.
- Missing Data: If you notice that some data (a month for ex.) is missing in your consolidated file or pivots, try refreshing the file a few times. If that doesn’t work, go to “Query” ribbon and “Edit” the query. Once you are in PQ Editor, backtrack and locate the step that might be causing the problem.
- Any other problem? Leave a comment with the issue you faced so I can offer some guidance.
Learn More 👩💻
You can use Pivot Tables + Power Query to do so many awesome things in Excel. Check out below tutorials to master the concepts.
💥 Complete Excel + Pivot Tables + Power Query course from Chandoo
If you want to learn all the necessary Excel functionalities and how to apply them for work in one place, check out my Excel School program.
Trusted by more than 20,000 people around the world, Excel School is the #1 course to help you slay Excel for good.
13 Responses to “Convert fractional Excel time to hours & minutes [Quick tip]”
Hi Purna..
Again a great tip.. Its a great way to convert Fractional Time..
By the way.. Excel has two great and rarely used formula..
=DOLLARFR(7.8,60) and =DOLLARDE(7.48,60)
basically US Account person uses those to convert some currency denomination.. and we can use it to convert Year(i.e 3.11 Year = 3 year 11 month) and Week(6.5 week = 6 week 5 days), in the same manner...
This doesn't work for me. When applying the custom format of [h]:mm to 7.8 I get 187:12
Any ideas why?
@Jason
7.8 in Excel talk means 7.8 days
=7.8*24
=187.2 Hrs
=187 Hrs 12 Mins
If you follow Chandoo's instructions you will see that he divides the 7.8 by 24 to get it to a fraction of a day
Simple, assuming the fractional time is in cell A1,
Use below steps to convert it to hours & minutes:
1. In the target cell, write =A1/24
2. Select the target cell and press CTRL+1 to format it (you can also right click and select format cells)
3. Select Custom from “Number” tab and enter the code [h]:mm
4. Done!
Hi, sorry to point this out but Column C Header is misspelt 'Hours Palyed'
good one
So how do I go the other way and get hours and minutes to fractional time?
If you have 7.5 in cell A1,
- Use int(A1) to get the hours.
- Use mod(A1,1)*60 to get minutes.
If you have 7:30 (formatted as time) in A1
- Use hours(a1) to get hours
- Use minutes(a1) to get minutes.
I had the same issue. You can solve it by changing the format as described above:
Right click cell > Format Cells > (In Number tab) > Custom > Then enter the code [h]:mm
([hh]:mm and [hhh]:mm are nice too if you want to show leading zeros)
Thanks guys, these are the tips I'm looking for.
...dividing the number of minutes elapsed by the percent change is my task - "int" is the key this time
It doesnt work for greater than 24 hours
It returns 1:30 for 25.5 hours. It should have returned 25:30
Ideally I would right function as
=QUOTIENT(A1,1)&":"&MOD(A1,1)*60
Sorry, replied to wrong comment....
----
I had the same issue. You can solve it by changing the format as described above:
Right click cell > Format Cells > (In Number tab) > Custom > Then enter the code [h]:mm
([hh]:mm and [hhh]:mm are nice too if you want to show leading zeros)
Clever use of MOD here to extract the decimal part of a number. Divide a number containing a decimal by 1 and return the remainder. Humm. Very clever.
Thanks very much, extremely useful !