Another question from the UCF Accounting Conference. JoAnne asks... XLOOKUP can return multiple columns. What if you want to return several non-adjacent columns, for example, January, April, July, October?
There are three solutions presented here.
Table of Contents
(0:00) XLOOKUP return non-adjacent columns
(0:30) XLOOKUP with FILTER
(2:39) Using HSTACK
(3:17) VLOOKUP with Ctrl+Shift+Enter
(4:40) How would you solve it?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
MrExcel Excel Videos
Learn Excel from MrExcel - Excel tips and tricks from Bill Jelen.
Filters
Show only:
Loading…
The World Cup is now in group stage. Each group has four teams. The top two teams advance to the knockout stage. This video explains the 7 tiebreaker steps, and uses Microsoft Excel to analyze the 729 permutations that can happen in any one group. How likely is it that the tiebreaker will need to be used?
Table of Contents
(0:00) World Cup Group Stage Scoring
(1:00) Tiebreaker rules
(1:50) Odds of a tiebreaker
(2:20) Excel Model for one group
(2:50) TEXTSPLIT function
(3:22) Excel Trace Dependents
(3:45) Scoring & Sorting
(4:10) Detecting a Tie
(4:41) Why 729 permutations
(4:53) 28% tiebreaker chance
(5:46) Six draws leads to 12 points
(6:04) 18 points total
(6:40) Which results have a tie?
(7:28) Excel behind the scenes
(8:00) 7...
In this video, several of my friends and I propose ways to cut a 75-minute Excel process down to under 15 seconds. As expected Diarmuid Early posts the fastest time with an under-10-second solution. But Diarmuid warns about a possible problem in the data, which makes a method proposed by Peter_SSs and Anup Agarwall the fastest and safest method.
The formula methods from others:
https://www.mrexcel.com/board/threads/excel-insert-blank-row-after-every-two-data-rows-shorts.1217116/
https://www.mrexcel.com/board/threads/insertblankat.1217374/
Table of Contents
(0:00) Hurricane Ian delay
(0:34) 2-minute solution for OP
(1:11) 53-Second solution
(1:30) Too Slow
(2:10) Fastest Excellers
(2:30) Anup Agarwal
(3:07) Diarmuid Early I
(4:06)...
Lasma wants her pivot table to group daily dates up to months and years. But she has some empty rows and the Group Field is greyed out.
In this video:
Surprise! Microsoft 365 now allows blanks in the date field and you can still group.
Solution 1: Fill blank date fields with 2199 December 31
Solution 2: Add your own Year and Month fields to the data set
And, a warning from Debra Dalgleish at Contextures: if you mix dates and text, the Group Field will still be greyed out.
Table of Contents
(0:00) Pivot Group Dates with Blanks
(0:15) Fix from Excel Team
(1:10) Select empty date cells
(2:09) Add Year Month to source data
(2:40) Month Name from date using TEXT
(3:30) Debra: mix of dates and text
(4:00) "empty" cells by spacing through...
Kelly wants to display a Delta in a Pivot Table. Unfortunately, Calculated Fields in a Pivot Table look at each individual row, so the MAX(B2)-MIN(B2) will always be zero.
In this video, a DAX Measure solves the problem. But to unlock DAX, you need to choose Add This Data To The Data Model.
Table of Contents
(0:00) Need Max minus Min in pivot table
(0:31) MIN and MAX are easy
(0:51) Calculated Field fails
(1:51) Add to Data Model
(2:51) New Measure
(4:25) Wrap-up
Brand new in Excel Insiders Beta:
A chart can be based on an entire dynamic array in Excel.
In this first iteration, the chart has to point to the entire array. If you have columns in your array that you don't want to chart, you should use =CHOOSECOLS to get a smaller subset of the array for charting.
Right-drag a selection in Excel and choose Create Hyperlink Here.
This is supposed to create a clickable hyperlink, but it does not seem to be working.
The reason? The workbook has to have a path and file name in order for it to work.
This video talks about the various tools in the Excel Alternate Drag and Drop Menu.
Table of Contents
(0:00) Excel - Create Hyperlink Here is broken?
(0:15) Alternate Drag and Drop Menu
(0:28) No hyperlinks when workbook unsaved
(0:56) Copy Here as Values Only in Excel
(1:15) Link Here in Excel
(1:27) Copy Formatting in Excel
(1:38) Copy Column Widths in Excel
(2:00) Why Create Hyperlink Not Working
(2:18) Excel Create Hyperlink Here
(2:35) Hyperlinking to a range of cells
(3:04) Specifying tooltip for...
Colleen calls with a question today. She has a pivot table with too many fields in the Values area. She can't easily resequence them. Is there a way to see more fields in the Pivot Table Fields pane?
Table of Contents
(0:00) Areas Section Only
(0:12) Undock & Resize Field List
(0:22) Drag line between Fields & Areas
(0:29) Re-dock pivot table fields pane
(0:39) Dragging field headings in pivot table
(0:50) Rearrange by typing
K has an line at the bottom of one cell. How can we remove it?
Other questions here:
Why does AutoFit Row Height not work in Excel?
Why can't I delete this border in Excel?
Deleting drawing objects in Excel?
Borders using Conditional Formatting in Excel?
How to remove page breaks in Excel?
Paste Picture Link in Excel?
Why is there one extra line of whitespace at the bottom of my cell?
How do I manually change row height in Excel?
Thanks to YouTubers: DropMeSort, Finnur Torfi Gunnarsson, Rico S., and Vesa Ruusunen.
Thanks to Debra Dalgleish at Contextures.
Table of Contents
(0:00) Remove the Line at the bottom of a cell
(0:19) Use Chapter Markers
(0:51) Remove Border
(1:53) Remove Conditional Formatting
(2:59) Click on the Drawing...
