Category: Power Query

Wildcards in Excel VLOOKUP XLOOKUP and SUMIFS – 2512

Posted on by Annie D

Mohammad asks: How do we vlookup part of a cell in part of another cell. For example, find “bank of america” in “you have received money from bank of america account”. This video shows how to solve this with VLOOKUP, XLOOKUP, and SUMIFS.

Topics here:
wildcard excel lookup
wildcards in excel formula
how to use excel wildcards
excel how to search wildcards
what are excel wildcards
wildcards excel search
excel wildcard search in formula
excel wildcard search not working
excel wildcards search
microsoft excel wildcards
microsoft excel wildcard in formula
wildcard not working excel
excel wildcards does not work
excel using wildcards
excel wildcard for text
excel wildcards vlookup
excel wildcard xlookup
excel wildcard zero or more characters
excel wildcard Continue reading “Wildcards in Excel VLOOKUP XLOOKUP and SUMIFS – 2512” →

Why Excel SUM is Zero #Shorts

Posted on by Annie D

Why Excel SUM is zero.
Why Excel Sum function not working.
Why Excel SUM returns 0.
Why Excel Sum is wrong.
Why Excel Sum shows 0.
Why Excel Not Showing Sum.
Why Excel Sum Formula Not working.

Do you have a column of numbers that won’t sum in Excel? It is most likely that the numbers have been stored as text. Here are two quick ways to convert those text numbers and the numbers will then sum.

One frustrating behavior when this happens: =SUM(A2:A4) is zero, but =A2+A3+A4 calculates correctly. That is because the plus sign coerces a number out of the text, but the SUM function is Continue reading “Why Excel SUM is Zero #Shorts” →

Two Excel Cells Look The Same Why Don’t They Match – 2511

Posted on by Annie D

Vineela is testing if two cells are the same with =IF(A2=B2. Both cells look exactly the same, but Excel is saying they don’t match! How to find why they don’t match.

Table of Contents
(0:00) Cells look alike
(0:20) =A2=B2
(0:33) Leading spaces
(0:45) F2 for trailing spaces
(1:20) Length & each character
(2:18) MID & ROW
(2:50) Five new books
(2:59) Get ASCII CODE
(3:20) Non-Breaking Space 160
(3:52) Formula to Fix
(4:50) Wrap-up

Automating your formatted tables in Power BI!

Posted on by Annie D

Wondering how you can take advantage of the Formatted Tables feature in Power BI and automate the output to email? Jason Himmelstein shows you how you can do this with Power Automate!

Connect with Jason:
https://twitter.com/jasehimm
https://www.linkedin.com/in/jasonhimmelstein/
https://bifocal.show/

Formatted Tables:
https://docs.microsoft.com/en-us/power-bi/paginated-reports/paginated-formatted-table

Social hour at Power Platform Conference:
https://link.bifocal.show/socialhour

Power Platform Conference
https://powerplatformconf.com/

📢 Become a member: https://guyinacu.be/membership

*******************

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.

🎓 Guy in a Cube courses: https://guyinacu.be/courses

*******************
LET’S CONNECT!
*******************

— Continue reading “Automating your formatted tables in Power BI!” →

Can Excel Translate Languages – 2510

Posted on by Annie D

Can Excel translate languages?
Part 1:
There is a built-in Translation tool on the Review tab in Excel. It shows the answers in a task pane, but it does a bad job of getting those answers back to the Excel grid.

Five new books were published this summer. Check them out at: https://www.mrexcel.com/products/

Part 2: You can use a small bit of Power Query code from Smozgur at MrExcel to solve the problem. Get that code from: https://www.mrexcel.com/board/excel-articles/using-power-query-in-excel-to-translate-data-using-the-google-translation-api.33/

Table of Contents
(0:00) Can Excel translate languages
(0:24) Translation on Review tab
(1:15) Paste back to Excel fails
(1:47) Five new books published
(3:00) Translating Continue reading “Can Excel Translate Languages – 2510” →

🔴 Power BI tips from the Pros – LIVE (Sept 10, 2022) (Member Chat 2nd Half)

Posted on by Annie D

Join us live as we answer your questions about Power BI!

💥 30 minutes: Open Q&A (Public)
💥 Then… Members Only Chat (Public can watch & Super Chat for Questions)

📢 RULES FOR Q&A: 📢

👉 Put a “Q:” in front of your comment to help us identify questions!
👉 Super chats take priority
👉 Do NOT re-post your questions! We’ll get to as many questions as we can.
👉 If you SPAM your question, you will be put in timeout

📢 Become a member: https://guyinacu.be/membership

*******************

Want to take your Power BI skills to the next level? We have training courses available to help you Continue reading “🔴 Power BI tips from the Pros – LIVE (Sept 10, 2022) (Member Chat 2nd Half)” →

Excel Mark Each Unique Value In A Different Color – 2508A

Posted on by Annie D

Automatically assign a cell color to each cell based on value. If there are ten unique values in a range, assign ten colors.

I had previously done this using VBA in an old video: “Assign A Unique Color Value”: Podcast #1718 from May 28, 2013.

In this video, I show how it is easier to use DOSE for Excel to color-code each name or value in a range.

Save 25% on DOSE for Excel using Coupon Code MrExcel25 at https://mrx.cl/getdose

Excel Sum Or Count By Color – 2508

Posted on by Annie D

Total the numbers in the red cells. Average the cells with green font. While I have previously shown how to do this with VBA, it is now possible to quickly do this using a new add-in called DOSE for Excel.

Use coupon code MrExcel25 to save 25% on DOSE. Shop at https://MRX.CL/getdose

Excel Count Words Characters Or Sheets – 2508B

Posted on by Annie D

How can you count words in Excel? Instead of copying the data to Word, use you can use the DOSE for Excel add-in. In this video, I show you how to:
Count words in a range.
Count characters in a range.
Count worksheets in a workbook.

Save 25% on DOSE for Excel using Coupon Code MrExcel25 at https://mrx.cl/getdose

Add A Blank Row After Each Subtotal Row – 2507

Posted on by Annie D

Holly has a data set with Subtotals. She needs to insert a blank row after each subtotal row and add a calculation to those blank rows.

Table of Contents
(0:00) Welcome
(0:15) Go To Special Tricks?
(0:47) Subtotals
(1:10) Helper column on Total rows
(1:50) Move 1’s down a row
(2:28) Select just 1’s & Insert Rows
(3:00) Select blank cells
(3:32) Entering the formula
(4:30) Favorite Go To Special

Excel How to Add Checkboxes – 2508F

Posted on by Annie D

Sometimes in Excel, you need checkboxes. Many checkboxes.
If you are designing a form and need checkboxes, this video will help.
All the way back in video 1871, I showed one method for getting checkboxes in Excel.
Today, I show how you can do this quickly and easily with the DOSE for Excel add-in.

In a perfect world, those Checkboxes would be tied to a set of cells, so you could use formulas to reach which ones are checked.

Also in this video, how to convert text in cells to an option buttons – this allows people to choose exactly one option, but not Continue reading “Excel How to Add Checkboxes – 2508F” →