To select the middle row in Excel, identify the center worksheet row, click its row number on the left, or place a cell cursor in that row and press Shift+Space. For rows 2 through 10, row 6 is the middle; if your range contains an even number of rows, it has two center rows, so you need to choose one.
What “middle row” means in Excel
Excel does not have a special command named Select Middle Row. You first decide what set of rows you mean, work out which row is in its center, and then select it using the row heading or a keyboard shortcut. The set might be a block of worksheet rows, a data range with a header, or a set of records that remains visible after filtering. Those are not always the same thing.
A middle row is based on the count and order of the rows in the set, not on the values in their cells. For example, the middle row of rows 2 through 10 is row 6 because there are four rows above it and four below it within that range. This is different from finding the median of a column, which identifies a middle value after considering the numbers.
Before selecting, decide whether headers, totals, blank lines, hidden rows, or filtered-out records belong to the set. If you include a header in the count, the answer may shift by one. If you mean the center of the whole worksheet, that is a different question: an Excel worksheet has 1,048,576 rows, so its central row is 524,288.
Find the center row in an odd-sized range
When a range contains an odd number of rows, it has one exact center. Count the rows in the range and divide by two, rounding up to find the center position. For a small block, counting is often quickest: rows 2 through 10 contain nine rows, and the fifth row in that block is worksheet row 6.
If you want Excel to calculate the worksheet row number, enter a formula in an unused cell. With the range running from row 2 through row 10, the formula is =INT((ROW(A2)+ROW(A10))/2). It returns 6, the worksheet row number at the center. The formula calculates a number; it does not automatically select that row.
This approach is useful when the range changes and you want to recalculate the center. Replace A2 and A10 with references in the first and last rows of your intended range. The referenced columns do not matter to ROW; the row portions do. Keep in mind that a formula using the first and last row only assumes that every worksheet row between them belongs to the range. If blank rows or excluded records should not count, identify the actual set before using the result.
Choose a center when the range has an even number of rows
An even-sized range has no single row exactly in the center. In a range of eight rows, the two middle positions are the fourth and fifth rows. You can choose the upper center, meaning the one with the smaller worksheet row number, or the lower center, meaning the one with the larger row number. State which one you need when documenting a repeatable process.
With a range from row 2 through row 9, this formula returns 5, the upper center (the lower-numbered of the two): =INT((ROW(A2)+ROW(A9))/2). The two center rows are 5 and 6. To return row 6 instead, use =ROUNDUP((ROW(A2)+ROW(A9))/2,0). These results come from the midpoint of the endpoint row numbers; they are not a count of nonblank cells.
For a general block, the same rule applies whether it starts at row 1, row 15, or another location. The key is to use the first and last worksheet row in the block, not just the number of data records. If the block includes a header that you do not want counted, start with the first data row instead.
Select the entire worksheet row
Once you know the row number, selecting the whole worksheet row is simple in Excel for Microsoft 365 on Windows:
- Find the row number in the headings along the left edge of the worksheet. If necessary, use the vertical scroll bar to bring it into view.
- Click that row heading. Excel highlights the full row across the worksheet, not just the cells currently visible on screen.
- To select several adjacent rows, drag across their row headings, or select the first heading and Shift-click the last heading.
You can also click a cell in the target row and press Shift+Space to select its entire worksheet row. On Mac, Shift+Space is also the row-selection shortcut in Excel. If the shortcut inserts a space or behaves differently, first click a worksheet cell rather than typing inside a cell or the formula bar.
For a precise target that is far down the sheet, use the Name Box to the left of the formula bar: type a reference such as A524288 and press Enter to go to that cell, then press Shift+Space. Alternatively, click the row heading after navigating there. The Name Box can also accept a whole-row reference such as 524288:524288, which selects that row. On Windows, Ctrl+G opens Go To; enter a cell reference, press Enter, and then use Shift+Space to select its row.
Select only the middle row within a data block
There is an important difference between selecting a worksheet row and selecting the cells that form a row in a table or data block. Clicking a row heading or pressing Shift+Space selects the entire worksheet row, including cells outside your table. That can be convenient for formatting a complete line, but it may be too broad if you intend to copy or edit only the data block.
To select only the cells in the middle row of a rectangular block, click and drag across the cells in that row. For example, if the data occupies columns A through F and the center worksheet row is 6, select A6:F6 rather than the row heading. You can type A6:F6 in the Name Box and press Enter to select that specific range. Adjust the columns to match your actual data.
Excel tables add another distinction: a table can have a header row, data rows, and possibly a totals row. Decide whether the center means the center of the data records only or the center of every displayed table row. Usually, when someone asks for the middle record, the header and totals row should not be included in the count. The worksheet row number of the resulting record can still be selected with the row heading, but that selects cells beyond the table as well.
Filtered, hidden, and blank rows
Filtering changes which records are displayed, but it does not renumber the worksheet or remove filtered-out records from the sheet. A midpoint calculated from the first and last worksheet row therefore refers to the middle row of that full span, not necessarily the middle visible record. Manually hidden rows create a similar distinction: they still exist in the worksheet even though they are not displayed.
If you need the center of visible records only, first define what counts as visible and count those records in their displayed order. A plain midpoint formula based on endpoint row numbers cannot account for filtered-out rows. For a short filtered list, you can inspect the visible rows and count to the center; for a changing list, a formula or workflow needs to explicitly handle visibility rather than assume every row in the range counts.
Also check for blank rows. A blank row inside a block counts as a worksheet row when you use the endpoints, even if it is not a data record. If your task is to select the middle populated record, decide which column reliably identifies a record and base the count on those records instead of treating all rows in the span as equivalent.
Common mistakes and useful distinctions
- Confusing a row with a cell: Clicking a cell selects one cell; clicking the row heading or pressing Shift+Space selects the whole worksheet row.
- Counting the header by accident: If the first row is a label row, include it only if it belongs in the set whose center you want.
- Expecting one center in an even range: There are two central rows. Choose the upper or lower one rather than treating either answer as uniquely correct.
- Using a value-based median: The middle worksheet row depends on position. The MEDIAN function calculates a central numeric value and does not select a row.
- Assuming the formula selects its result: A row-number formula only returns a number. Navigate to that row and select it separately.
For one-time work, counting and clicking a row heading is usually the clearest method. For repeatable work, calculating the row number makes the choice explicit, especially when the endpoints change. If you are going to format or delete the row, verify that the highlight covers the intended row or data cells before taking an action that affects the workbook.
Frequently Asked Questions
How do I select a whole row in Excel? Click its numbered heading at the left of the worksheet, or click a cell in the row and press Shift+Space. The row heading method makes it visually clear that the entire worksheet row is selected.
How do I select the middle row of a range in Excel? Identify the first and last rows in the range, count the rows, and locate the center position. For an odd count there is one center row; for an even count choose between the two central rows, then select it by its heading or with Shift+Space.
What is the middle row between rows 2 and 10? Row 6. The inclusive range from row 2 to row 10 has nine rows, and row 6 is the fifth row in that range, leaving four rows on either side.
How do I select the middle row if there are an even number of rows? Choose one of the two central rows. For rows 2 through 9, rows 5 and 6 are both central; row 5 is the upper, lower-numbered center, and row 6 is the lower, higher-numbered center.
Does Excel have a shortcut specifically for the middle row? No. Excel provides ways to select a row, such as clicking its heading or using Shift+Space, but it does not automatically determine the middle of an arbitrary range and select it. You must define the range and find its center first.
Is selecting the middle row the same as finding the median? No. Selecting a middle row is about the row’s position in an ordered set. A median is a statistical value based on numbers; it may not be located in the middle worksheet row unless the data has been arranged to make that so.
Related
- Correlation Table in Excel: Create and Read One
- Python Pandas Excel Export: Methods and Examples
- CSV File to Excel Converter: Convert Without Data Loss
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Put a Flowchart in PowerPoint: SmartArt or ShapesTo put a flowchart in PowerPoint, use Insert > SmartArt > Process for a quick, editable diagram, or…
- Microsoft PowerPoint: How to Record a PresentationTo record a presentation in Microsoft PowerPoint for Microsoft 365 on Windows, open the deck, select Record >…
- How to Unhide Rows in Excel That Won’t UnhideIf Excel’s Unhide Rows command does nothing, first select the rows on both sides of the missing section…
- Best Way to Export Outlook Contacts as CSV or PSTFor most people, the best way to export Outlook contacts is to save them as a CSV file:…
- Where to Insert a Table of Contents in WordIn Word, insert a table of contents (TOC) where you want it to appear—usually near the beginning of…
- Out of Office in Outlook Desktop: Set Up Your ReplyTo set an out-of-office reply in Outlook desktop, open File > Automatic Replies in classic Outlook for Windows,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.