Look Who it Was Random video

Excel - Dueling Excel - Copy Down for Across: - Episode 1298

1,659 views · Published 3 December 2010 · 5:14 · Indexed 26 September 2026

Channel: MrExcel.com · 2010 · Education

Watch on YouTube

Microsoft Excel Tutorial: Dueling Excel - Copy Down for Across:.

Welcome to another episode of the Dueling Excel podcast! I'm Bill Jelen from MrExcel and I'm joined by Mike Girvin from Excel Is Fun. Today, we're tackling a question about copying formulas down and across in Excel. Our viewer wants to create a median of test scores, but they want to be able to copy the formula down and have it go across a column each time. So, let's see how we can solve this using the OFFSET function.

First, we'll use the OFFSET function to specify the range for our median formula. This amazing function allows us to do so many different things in Excel. We start by selecting the top left corner cell, in this case A8, and pressing F4 to lock it down. Then, we specify how many rows down and columns over we want to go to get to the starting point of our range. For the rows, we'll use 0 since we want to start in the same row each time. For the columns, we'll use the world's geekiest way to write the number 1 - "row of A1". This will give us a 1 in the first row, 2 in the second row, and so on as we copy the formula down. We'll then specify the height and width of our range, in this case 12 rows tall and 1 column wide. This will allow us to copy the formula down and have it automatically change to the next column each time.

Now, let's see what Mike has come up with. He's using the INDEX function instead of OFFSET. This function can look up a whole column and return a specific row and column within that column. So, for each test, we'll tell the INDEX to look up a different column, starting with column 1 for test 1 and increasing by 1 for each test. Mike also shows us how we can use the MATCH function to find the column number instead of using the "row of A1" trick. This is helpful if our data is not in the same order as our tests. The MATCH function will look for the test name and return the column number, which the INDEX function will then use to return the whole column of data.

So, there you have it - two different ways to copy formulas down and across in Excel. Thanks for tuning in to another episode of the Dueling Excel podcast. Don't forget to subscribe to our channels for more Excel tips and tricks from MrExcel and Excel Is Fun. 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-on-youtube/


Table of Contents:
(00:00) Copying formulas across rows and columns
(00:14) Using OFFSET function to copy formulas across columns
(01:36) Using INDEX function to copy formulas across columns
(04:53) Using MATCH function to lookup column numbers for INDEX function
(05:03) 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:
Copy Down for Across
Evaluating formulas
Exact match
Excel podcast
Formula copy down
Index function
Match function
Median of test scores
Medium function
Offset function
Row of A1
Two-way lookup
YouTube video transcript search terms:


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


Today, in Episode #1298, Bill "MrExcel" Jelen and Mike "Excel Is Fun" Girvin set out to create a 'median' of test scores by moving down rows and across column by simply working in the Formula Bar. Also the F9 Evaluation Feature is highlighted today!

More from this channel