Wrepit

🎅📈 Power Query - useful with tables✨🔥 PowerQuery - a powerful but scary feature in Excel AND PowerBI. Learn how power query can be used to combine simple Excel tables into massive data gathering tables - for automation of your Excel workbooks!

Yesterday we talked about tables. In the picture below, you can see that we have created 3 simple tables, for which we want to fetch some data:
- Employees
- Years
- Data Points (for stuff we want to know about each employee)

Imagine we have a table in another worksheet with a lot of data points from different employees from different years, and that we want all combinations of the above 3 tables in a new worksheet? This is a great case for Power Query! This is quite advanced, and we can split it into the following 5 steps:
1. Define the tables (already done)
2. Modify each table in PowerQuery, adding a "Dummy" column of "1" values to each row, and store with a temporary name.
3. Merge all 3 tables together, on the "Dummy" value, with a "Full outer join".
4. Save and Close to store the Query as a new Table in Excel.
5. Use the table in a formula 🖥️=MyNewQuery

🔥Step 2: Right-click your table and click "Get Data from Table/Range", which will open PowerQuery. In the "Add Column" tab, add a "Custom Column". Name the column "Dummy" and set the custom formula to "=1". Rename the query to something helpful, like "YearsWithDummy". Do this for all 3 tables.
🔥Step 3: Within PowerQuery, right-click the "Queries" area, click "New query", then "Combine" and "Merge queries as New". Select e.g. YearsWithDummy and EmployeesWithDummy, click the dummy column in both, and select the "Full Outer Join" option and then "Ok" to apply. The "EmployeesWithDummy" column can then be expanded by clicking the "expand arrows" in the column (no need to keep Dummy). Then find "Home" tab and "Merge queries" to add DataPointsWithDummy as well to the table, and finally remove unnecessary dummy columns.
🔥Step 4: Give your query a useful name (like "EmployeeDataPerYear") and hit "Save and Close".
🔥Step 5: Excel will now have a new tab for the new query, and you can reference the query by name in Excel formulas! Combine this with other functions and data sources and you can fully automate your data collection. 📣✨🚀 (In the image below, we have applied this formula to cell C14: 🖥️=EmployeeDataPerYear)

Follow Wrepit, like and reshare for more tips on Excel and Word this December! 🚀👍

🎅📈 Change number separators with ease in Excel2Word! ✨🔥

Sick of pulling your hair out trying to format numbers correctly? Trust us, we've got your back!

Introducing the Excel2Word add-in - your secret weapon for effortlessly changing number separators in a snap! 💪 Just click your name, head to preferences, and click on the "Override number separators" toggle. Once you've found the right format, simply sync and save to achieve number formatting perfection.

Follow Wrepit, like and reshare for more tips on Excel and Word this December! 🚀👍

🎅📈 Want to make calculating totals on stacked bar charts in a piece of cake? Let us show you how! 💪

Once you have your stacked bar chart ready, head over to "Chart Design" on the toolbar and click "Select Data". Add a new series, give it the name "Total", and enter the Y-Values from your table under the Total column. You should now have a a new bar on top of each category.

Select the top bar (the new "Total" series) and change the Chart type to "Line chart". Right-click on the line, select "Add data labels", and voila! Numbers appear on top of each bar.

Next, go to "Format data series", click the paint bucket, and click "No line". The line disappears, leaving you with a clear view of the totals for each serie.

Now you're ready to impress with your total-tallying skills! 🌟

Follow Wrepit, like and reshare for more tips on and this December! 🚀👍