Searching ways to Automatically Unmerge Cells in Excel? In this post I’ll show you a free Excel tool that will Unmerge all the cells and fill the blanks with values to help you analyze your data!
Free Download
Download now for free our Excel Tool used in the video below:
Unmerge Cells in Excel Automatically
Watch this video and learn how to use our tool to Unmerge all the cells automatically:
How does the tool work:
The tool allows you to quickly Unmerge all cells in Excel and fill the blanks with values after two simple steps:
Step 1: Paste the merged table into the tool
Step 2: Press the 'Run' button
Your table has now been unmerged and the blanks filled with the correct cell values!
I’m an Engineer and Data Analyst, and I moved to Germany in 2018 to work for one of the biggest logistics companies in the world. Hacks for Data Analysis is the place where I consolidate and share my knowledge, feel free to reach out to me. Cheers!
Searching how to do VLOOKUP in Power BI? In this post I’ll show you how to use the Merge Queries function from Power BI to achieve this!
Free Power BI Template Download
Download now for free our Power BI template used in the video below:
Merging Queries in Power BI
Watch this video and learn how to do VLOOKUP in Power BI using the Merge Queries functionality:
How does the 'Merge Queries' work:
The Merge tables functionality from Power BI allows you to select common columns between 2 or more different tables, creating one single “Merged” table based on the selected column.
Table 1
Table 2
Table 3 = Table 1 merged with Table 2 by common 'Column B'
Easier than Excel's VLOOKUP!
Power BI’s Merge Queries is fairly easy to use, and with just a few clicks in the tool, allows you to extract multiple columns from multiple queries to one single query.
The Merge Queries in more details
You can find here more details on Microsoft’s webpage on the Merge Queries from Power Query which is available in Power BI.
I’m an Engineer and Data Analyst, and I moved to Germany in 2018 to work for one of the biggest logistics companies in the world. Hacks for Data Analysis is the place where I consolidate and share my knowledge, feel free to reach out to me. Cheers!
Watch this video and learn 2 Ways to Group DATA in Power BI by using visuals or DAX formula:
You can use this approach to
Understand a population into different Age Groups
Categorize products into different ranges of price
Split a list of invoices aging into intervals
The 2 Ways in more details
You can find here Microsoft’s documentation on user grouping and binning for more details on the first method from the video. For the second method, you can find here in details more information on the conditional “IF” DAX function.
I’m an Engineer and Data Analyst, and I moved to Germany in 2018 to work for one of the biggest logistics companies in the world. Hacks for Data Analysis is the place where I consolidate and share my knowledge, feel free to reach out to me. Cheers!
I’m an Engineer and Data Analyst, and I moved to Germany in 2018 to work for one of the biggest logistics companies in the world. Hacks for Data Analysis is the place where I consolidate and share my knowledge, feel free to reach out to me. Cheers!
How to Calculate Age (Days, Months or Years) in Excel
During this week we posted our first video from the Hack of the Week series where we showed you How to Calculate Age in Excel in Days, Months or Years. The trick shown in the video is a trick to combine Excel’s hidden function DATEDIF and IFERROR to create a new formula, ensuring a correct calculation of age in days, months or years without errors.
Â
The DATEDIF function
Using the DATEDIF function on its own may result in a few errors and limitations, however our trick will allow you to use one single formula every time to calculate the correct age in days, months or years without errors.
Free Template and Video Tutorial showing How to Calculate Age in Excel
Â
You can download now for free the Excel template with the formula demonstraded in the video below.
Other variations of the DATEDIF function
In addition to the example given in our video above, you can also try a few more variations of unit other than “Y“, “M” and “D” as detailed in Microsoft’s documentation and shown below:
DATEDIF(start_date,end_date,unit)
For instance, you can use =DATEDIF(“15-Jan-2019″,”1-Mar-2020″,”YM”) to calculate the number of complete months between 15 January and 1 March, which will result in 1 full month.
I’m an Engineer and Data Analyst, and I moved to Germany in 2018 to work for one of the biggest logistics companies in the world. Hacks for Data Analysis is the place where I consolidate and share my knowledge, feel free to reach out to me. Cheers!
Do you work in an international environment where your colleagues have Excel installed in different versions and languages? Or perhaps – like in my case – you moved from a different country and now you need to relearn how to use Excel and its formulas, however it’s all in English this time. A Google Translate for Excel could be quite handy at these times! In this post I’d like to share about the Excel Translator, the free web Google Translate for Excel tool that has helped me BIG TIME since I moved from Brazil to Germany in 2018 for a job.
But this doesn’t work for me, my Excel is in German!!!!
It’s very handy to have this under your sleeve!
I am much more familiar now with working in Excel in English than in any other language. But I still find myself sometimes at situations where, for instance, I need to demonstrate some Excel Hacks, tricks and tips to someone that has their Excel installed in German.
I want to be able to solve these kind of issues at anytime without a hassle, therefore the Excel Translator works perfectly for me!
How to use the Google Translate for Excel
Learn how to use the FREE Web Excel Translator tool by following the 3 steps below:
I’m an Engineer and Data Analyst, and I moved to Germany in 2018 to work for one of the biggest logistics companies in the world. Hacks for Data Analysis is the place where I consolidate and share my knowledge, feel free to reach out to me. Cheers!
