Category: Power Pivot

Excel Variance Inside The Pivot Table – Episode 2605

Posted on by Annie D

Microsoft Excel Tutorial: Adding a Variance inside of a pivot table.

Keani has a pivot table with current year and last year across the top. She wants a variance. Her current solution is a formula outside of the pivot table which points inside of the pivot table.

This video shows three methods for adding a variance inside the pivot table.
1. Regular pivot table, add Revenue twice. Change calculation to Difference From, Years, and (Previous Item)
2. Regular pivot table. Remove Grand Total. Use 2 Excel Calculated Items to calculate Variance and Total
3. Data Model Pivot Table. Add four DAX Measures to calculate last Continue reading “Excel Variance Inside The Pivot Table – Episode 2605” →

Excel All Combinations One To Six in Four Columns – Episode 2604

Posted on by Annie D

Microsoft Excel Tutorial: Generating all combinations of N outcomes for K games.

In episode 2602, I had to generate all possible combinations of 4 games with 6 possible outcomes in each game. I used my very convoluted “binary count up, but not binary because it is base 6, but not base 6 because I need the digits 1 to 6 instead of 0 to 5” method. This involves a lot of typing and two different formulas.

Today, a much easier way from Kyle Freistedt. Kyle DOES use Base 6. He does use a SEQUENCE function but starts at 0 instead of Continue reading “Excel All Combinations One To Six in Four Columns – Episode 2604” →

Excel – Circle Invalid Data – #shorts

Posted on by Annie D

Microsoft Excel Tutorial – Circle invalid data in Excel

This video shows a cool use for Data Validation: Circling Invalid Data.

Also: manually circling a cell in Excel using the drawing tools. This method creates a filled circle. This video shows you how to remove the fill so you have a circle that does not cover the data.

To download this workbook: https://www.mrexcel.com/youtube/OpVReJuDvWs/

Table of Contents
(0:00) Circle invalid data in Excel
(0:08) Define valid data in Data Validation first
(0:28) Manually drawing circles
(0:38) Making filled circle an outline
(0:46) Ctrl+Drag to copy circles in Excel
(0:52) Wrap-up

This video answers these search terms:
how do you circle Continue reading “Excel – Circle Invalid Data – #shorts” →

Excel VBA Window is All Grey Or Panes Will Not Redock Correctly – Episode 2603

Posted on by Annie D

Microsoft Excel Tutorial: The VBA Editor Panes are missing or not docked correctly.

Chris writes in. His VBA Editor is completely grey. How does he get back to the classic view with the Project Explorer in the Top Left, the Properties window in the bottom left and the Code on the right?

To download this workbook: https://www.mrexcel.com/youtube/C4T9CDfVA5g/

Table of Contents
(0:00) Program Note
(0:11) Excel VBA Editor is all Grey
(0:50) VBA Panes not docking correctly
(0:55) Undocking VBA Pane
(1:08) Look for “Ghost” panel to redock
(1:28) Excel VBA Pane redocks full width
(1:46) VBA Editor no room for code pane
(1:56) Undock the Project Explorer
(2:08) Continue reading “Excel VBA Window is All Grey Or Panes Will Not Redock Correctly – Episode 2603” →

Excel – How to Draw a Circle in Excel #shorts

Posted on by Annie D

Microsoft Excel Tutorial – Draw a Circle in Excel

The drawing tools in Microsoft Excel offer an oval. There is a secret trick to making the oval into a perfect circle: Hold down the Shift key while drawing.

To download this workbook: https://www.mrexcel.com/youtube/vUtrQeqxqRA/

Table of Contents
(0:00) Draw a Circle in Excel using Shift Key
(0:22) Draw Many Circles in Excel
(0:33) Circle something on a chart in Excel
(0:44) Remove Fill in Excel circle

This video answers these common search terms:
how do you draw in a circle in excel
how to draw a circle in excel
how to draw circle in excel
how to draw circles in Continue reading “Excel – How to Draw a Circle in Excel #shorts” →

Jeopardy Masters Semi-Finals Tie! What Are The Odds – Episode 2602

Posted on by Annie D

Mattea Roach and Andrew He finished the Jeopardy Masters Semi-Finals in a tie. Mattea Roach advanced thanks to a complex tie-breaker. What are the odds that the contestants who advanced to the finals would be decided by a tie-breaker?

It turns out that 18.5% of the possible outcomes would have required using a tie-breaker.

This video explains the math behind it all and the Microsoft Excel model used to make the calculation. To download the workbook: https://www.mrexcel.com/youtube/SUXTNAp4XgU/

Table of Contents
(0:00) Spoiler Alert
(0:21) Tie game is like kissing your sister
(1:06) Odds of a tie in Jeopardy Masters Semi-Finals
(1:33) Continue reading “Jeopardy Masters Semi-Finals Tie! What Are The Odds – Episode 2602” →

Excel – Draw a Semi-Circle

Posted on by Annie D

Microsoft Excel Tutorial – how to draw a semi-circle in Excel.

The Insert Shapes drop-down menu in Excel offers an oval, but not a semi-circle. How can you draw a semi-circle? Most of those shapes include a yellow handle for changing the inflection point. You can use this handle to make the partial circle into a half-circle or quarter circle.

Download the workbook from today: https://www.mrexcel.com/youtube/U2gvZtUW0vk/

This short video shows you how.

Table of Contents
(0:00) Draw a semi-circle in Excel
(0:15) Yellow handle
(0:27) Rotation handle

This video answers these common search terms:
How to draw a semi-circle in Excel
How to draw a half Continue reading “Excel – Draw a Semi-Circle” →

Excel – Embed Growing Range In Word Document – 2601

Posted on by Annie D

Microsoft Office Tutorial – live link to Excel range in Word.

Ashley wants to embed a range of Excel data in a Word document.

If the data in Excel changes (including adding more rows), she wants the Word document to update.

Annoying: If you use Insert, Object in Word, it captures the wrong range and truncates if you add more data.

My method: (1) Set the Print Range in Excel, (2) Select the Print range and Copy. (3) Paste to Word, (4) Open the paste drop-down menu and choose Linked Keep Source Formatting. This reliably works.

How do you solve this? Let me know down Continue reading “Excel – Embed Growing Range In Word Document – 2601” →

Excel VLOOKUP Fails At Strange Hyphen – 2600

Posted on by Annie D

Microsoft Excel Tutorial – Deep Dive on diagnosing VLOOKUP errors.

To download the workbook: https://www.mrexcel.com/youtube/_q0VdM1ES1g/

This is an advanced look at troubleshooting hard VLOOKUP errors. After using TRIM, CLEAN, and making sure there aren’t numbers stored as text, then I use this method to look at the ASCII code, character by character to figure out why the two values do not match.

But today, the vendor part number is reporting that a hyphen is ASCII CODE 63 instead of 45. What is up with this?

I had to modify my workflow to add UNICODE functions in order to discover that the Continue reading “Excel VLOOKUP Fails At Strange Hyphen – 2600” →

Excel Labs Creates A LAMBDA From Existing Worksheet Logic – Episode 2599

Posted on by Annie D

Microsoft Excel Tutorial – Easiest Way to Create a Lambda function in Excel.

A new version of the Advanced Formula Environment from Excel Labs has been released. It has an amazing new functionality called Import from Grid.

There are many times where I will build a complicated Excel formula in sub-formulas. I finally combine all the sub-formulas into one mega-formula by scooping the subformulas out of the formula bar and pasting in to the final formula.

The new Advanced Formula Envionment, found in the Excel Labs add-in, offers to create a LAMBDA function from existing worksheet logic in the grid.

Table of Contents
(0:00) Continue reading “Excel Labs Creates A LAMBDA From Existing Worksheet Logic – Episode 2599” →

Excel for Mac New AddIn Brings More Data To Power Query – 2598

Posted on by Annie D

Power Query for Mac Excel is missing From Table/Range.
Power Query for Mac Excel is missing From Folder.
Power Query for Mac Excel is missing From XML.
Power Query for Mac Excel is missing From JSON.

A new More Data add-in from Suat Ozgur at MrExcel adds all four of these functionalities to Excel for Mac! Download the add-in from this article: https://www.mrexcel.com/board/excel-articles/morequery-for-mac.68/

The FREE, open-source add-in adds From Table/Range to Power Query for Mac. It adds From Folder. It adds From XML. It adds from JSON.

Table of Contents:
(0:00) More Data Add-In fixes Power Query for Mac
(0:37) Get Data From Table/Range Continue reading “Excel for Mac New AddIn Brings More Data To Power Query – 2598” →

Excel Technique Refer To Every Third Column From Bob Umlas 2583

Posted on by Annie D

Microsoft Excel Puzzle and Solutions.
Bob Umlas sent in the puzzle. How to refer to every 3rd column on Sheet2?

Table of Contents
(0:00) Bob Umlas Book
(0:22) Problem: Refer to every 3rd cell
(0:44) CHOOSECOLS with SEQUENCE
(1:16) Horizontal XLOOKUP
(1:46) Fill Handle from 3 Cells
(2:32) Delete Empty Cells
(2:54) R1C1 Method 2
(3:43) Replace = with x=
(4:12) Change R1C1 to A1
(4:21) Insert colums/rows to test