Excel - Inverting a Range in Excel: Dueling Excel | Excel Tutorial - Episode 1015

Microsoft Excel Tutorial: Inverting a Range in Excel: Dueling Excel.

Welcome to another dueling Excel podcast! In this video, Bill Jelen from MrExcel and Mike Girvin from Excel Is Fun will be showing you two different ways to invert a range in Excel. This is a common task that many Excel users encounter, and we're here to help you find the best solution.

The web question for today's video comes from a YouTube viewer who wants to know how to invert a range of data in Excel. In other words, they want the last cell to become the first cell and the first cell to become the last cell. Bill will be demonstrating his method using a formula, while Mike will be showing a different approach using the INDEX and ROWS functions.

Bill's method involves creating a new range called "inverted" and using the INDEX function to handle an array. He uses the ROW function to determine the number of rows in the original data set and then subtracts that number from 25 to get the correct row number for the INDEX function. This formula is live and will update if the data is sorted in a different way.

Mike's method is even geekier, as he uses the IINDEX function and the ROWS function to achieve the same result. He also highlights the entire range and uses a relative reference for the top cell and an absolute reference for the bottom cell. This allows the formula to be copied down and still give the correct result. Mike credits a viewer in the comments section for this formula.

Both methods are efficient and will give you the same inverted range. Bill's method uses the COLUMN function, while Mike's uses the COLUMNS function, as they are copying across columns. We hope you found this video helpful and will use these tips in your own Excel projects. Thanks for watching and don't forget to subscribe to both MrExcel and Excel Is Fun for more Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...


In today's dueling podcast, how to turn a range upside down. Episode #1015 shows you how.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Table of Contents:
(00:00) Introduction by Bill Jelen and Mike Girvin
(00:26) Web question from YouTube about inverting a range of data
(00:39) Bill's solution using the INDEX function
(01:48) Mike's solution using the ROWS function
(03:47) Comparison of both solutions and additional tips
(04:47) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
COLUMN function in Excel
Excel array formulas
Excel data analysis
Excel data manipulation
Excel formulas
Excel tips
Excel tricks and techniques
INDEX function in Excel
Inverting range in Excel
MrExcel and Excel Is Fun dueling podcast
ROW function in Excel
Sorting data in Excel


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...