Author: Jessica

I am an Excel and Access VBA Developer with years of experience in developing customized solutions to automate complex business processes. I am highly skilled in creating user-friendly interfaces that enable clients to easily interact with and analyze their data.

How to Remove Leading Apostrophes in Excel (2024)

If you have worked on Excel files extracted from a database or a system, you probably have encountered cells with leading apostrophes. If you’re like me, you too might have wondered why in the world they’re there.

It turns out that the apostrophe (or single quote) (‘) that you see at the beginning of a cell is actually a special character. It tells Excel to treat the remaining contents of the cell as text.

A cell is automatically treated as a text (regardless of its contents — may it be a date or a number) if it begins with an apostrophe.

What’s interesting about this character is that you don’t immediately see it when you look at the cells. You only see it from the formula bar (as shown below).

Leading apostrophe in a cell can only be seen from the formula bar

Adding this special character is useful if you intend to:

  • Retain the leading zeroes in numbers (e.g., 000421).
  • Add the month and year without having Excel automatically convert it as a date.

On the other hand, if you intend to use these cells on formulas or if you want to change the formatting of these cells, you will need to first remove the leading apostrophes. This step is crucial. Missing this will cause your formulas or cell formatting to not work as expected.

Below are varying methods that you could choose from to remove these leading apostrophes. Each of them has its pros and cons – so choose whatever best fits your needs.

1. Using“Text to Columns” option

This method is perfect if there’s only one column you need to convert or remove the leading apostrophes from. If you need to convert more than one column, please proceed to the next options.

Example of a column with leading apostrophes

1. Highlight the entire column you need to convert (or remove the leading apostrophes from).

Highlight the entire column to convert (or remove the leading apostrophes from)

2. Click on the Data menu. Go to the Data Tools section and click on Text to Columns.

Steps to access Text to Columns option

3. The Convert Text to Columns Wizard will appear.

From the Convert Text to Columns Wizard, select Delimited and click Finish.

From the options, select Delimited and click Finish.

4. That’s it! All the leading apostrophes inside the cells should be removed, and your column should now be in its correct format.

Sample output after running the Text to Columns wizard

In my case, the column has been converted into a date format.

2. By multiplying the cells by 1

If you’re looking at removing the leading apostrophes from cells containing numbers, this method is for you.

IMPORTANT: This method does not work on cells containing dates and texts.

Example of cells containing numbers and leading apostrophes

1. Type ‘1’ to any cell in your worksheet.

Type '1' in any cell on your worksheet

2. Select that cell and press CTRL + C to copy.

Copy cell containing '1'

3. Select all the cells with leading apostrophes.

Select all cells with leading apostrophes

4. Press CTRL + ALT + V to open the Paste Special menu.

From the Paste Special menu, select Multiply and click OK.

From the Operation options, select Multiply and click OK.

5. That’s it! You should see the selected cells converted into numbers.

Sample output after multiplying cells by 1

Related Tutorial: How to Remove Commas in Excel

3. By copying and pasting the cells as values

This method is probably my favorite as it works on any cell content – a number, a date, or a text. It also works on multiple columns.

The only requirement for this method is that you must copy the cells to new columns. I would recommend pasting them all together onto a new sheet so that the formatting of the cells is in the default format, which is General.

1. Highlight all the cells with leading apostrophes. You could include the headers if you want to.

Highlight all cells with leading apostrophes (including the headers)

2. Press CTRL + C to copy.

3. Go to a new sheet.

Go to a new sheet

4. Select the topmost cell of the column where you’d like to add the cells with no apostrophes.

Select the topmost cell of the column where you’d like to add the cells with no apostrophes.

5. Press CTRL + ALT + V to open the Paste Special menu.

From the Paste Special Menu, select Values and click OK.

From the Paste options, select Values and click OK.

6. That’s it! All the leading apostrophes should now be removed from the cells.

Sample output after pasting the cells as values

You can now add the appropriate cell formatting for each column.

You can now also use these cells in your formulas.

Related Tutorial: How to Remove Leading Zeroes in Excel

4. Using the VALUE() and DATEVALUE() functions

If you prefer to preserve the cells with leading apostrophes, and instead, add formulas to convert them, you can make use of the VALUE() and DATEVALUE() functions in your formula.

IMPORTANT: This method only works on numbers and dates. It does not work on texts.

These two functions are practically the same. The only difference is that the DATEVALUE() function only works on cells that contain texts that look like dates.

The VALUE() function can work on both numbers and dates.

These two functions basically convert a text string into its numerical version. 

In my example below, notice that cells A to D are all in text format because they have a leading apostrophe in the cells.

Example of cells with leading apostrophes

My goal is to get the total amount by multiplying the No. of Units with the Price without changing the format of these two columns.

To do this, I’ll use this formula: =VALUE(C2) * VALUE(D2)

Sample formula using the VALUE() function to convert cells in text format

As you can see, the correct value still appears even if the cells referred to are in text format.

On the other hand, if you want to convert a date in text format into an actual date, you can use either the VALUE() or DATEVALUE() function.

In my example below, I used the DATEVALUE() function. Notice that the result are all numbers.

Sample formula using the DATEVALUE() function to convert cell in text format

These numbers represent a date in a Microsoft Excel date format, between 1/1/1900 or 1/1/1904 (depending on the workbook’s date system) and 12/31/9999.

The next step is to convert this column into date format so that they will all look like actual dates.

Select the entire column and press CTRL + 1 to open the Format Cells menu.

From the Format Cells menu, select the appropriate date format and click OK.

From the list of Categories, select Date.

Then select the type or date format that you prefer and click OK.

Sample output after changing the cell format to Date

That’s it! The dates in text format are now converted into actual dates.

5. Automatically remove leading apostrophes using VBA

If you regularly receive files with leading apostrophes and would prefer having a macro to do the conversion or the apostrophe removal for you, then this final method is for you.

1. Activate the worksheet that contains cells with leading apostrophes.

2. Press ALT + F11. This should open the VBA Editor.

3. Click on the Insert menu and select Module.

From the VBA Editor, click Insert and select Module.

4. A new module will be inserted.

5. Copy the following code and paste it into this module.

Sub RemoveApostrophesInActiveSheet()
    Dim cl As Range
    
    With ThisWorkbook.ActiveSheet
        For Each cl In .UsedRange
            If cl.Value <> "" Then
                cl.Value = Replace(cl.Value, "'", "")
            End If
        Next
    End With
End Sub
Paste code into the new module

6. Once pasted, press F5 to run the code.

7. Close the VBA editor. You should see all the leading apostrophes removed from the active sheet.

If you want to do it on another sheet, activate that sheet first.

Open the VBA Editor again by pressing ALT + F11.

Press F5 to run the code. That’s it!

Related Tutorial: How to Remove Parentheses in Excel

Conclusion

The apostrophe at the beginning of a cell serves the special purpose of converting the cell content into text. If, however, you prefer converting the cell into its actual format, you can easily remove the apostrophe using any of the options provided above.

How to Calculate Cumulative Percentages in Excel?

A cumulative percentage is a statistical tool commonly used in analyzing scientific research, finance, and sales data. But what exactly is it, and what is its purpose?

Cumulative Percentage is defined as a way of expressing frequency distribution. It shows the percentage of the cumulative frequency within each interval.

Essentially, it can be viewed as the percentage of the accumulated total over time (or over certain intervals).

It can be used to understand how much progress has been made over time. 

If you look at the image below, the first two columns show the total sales for each month. The third column is the Cumulative Frequency (or the running total). The fourth column shows the Cumulative Percentage (or the percentage of the accumulated totals).

Table containing sample monthly sales data with Cumulative Frequency and Percentage

Looking at this table can already give us an idea of how much progress has been made by this pseudo business over time.

  • When it started in January, it only earned 5% of what it is making today (December).
  • After 6 months (or in June), it earned more than half (52%) of the overall total. 

As you may have noticed, the Cumulative Percentage gives us a better visual representation of the growth (as compared to the Cumulative Frequency).

Please know that Cumulative Percentage is not only limited to date intervals.

If you look at the table below, the first two columns show the number of students that earned a particular exam score. The last two columns show the Cumulative Frequency and Percentage.

Table containing census of students who earned a certain score on a pseudo exam with Cumulative Frequency and Percentage

Looking at this table, we can conclude that 72% of the students only got a score of 70 and below, which could mean that 72% of them didn’t pass (if 80 is the passing score).

As you can see, cumulative percentages can be used in varying datasets and can be interpreted in many ways.

Now, I’ll show you how you can calculate the cumulative percentage of your data in Excel.

1. Using Formulas to Calculate Cumulative Percentage

1. Set up your dataset. Add the interval on the 1st column and the value on the 2nd column.

Sample dataset with interval on the first column and the value on the second column.

Remember to sort the records in the correct order. It should be sorted from oldest to newest if it’s a date interval.

2. Now, we’ll add the formulas of the Cumulative Frequency in the 3rd column.

On the first cell, write this formula: =B2

Add formula to the first cell of the Cumulative Frequency column.

Note that it should point to the first value in the dataset.

On the second cell, write this formula: =B3+C2

Add formula to the second cell of the Cumulative Frequency column.

This formula sums up the current amount and the cumulative frequency of the previous row.

Next, copy this formula to the remaining rows in the column using the Fill Handle.

Click on the cell with the formula and hover your mouse on the right side of the cell until the Fill Handle appears (or until the mouse cursor turns into a black plus sign [+]).

Once you see it, drag your mouse down until you reach the last row in your data set.

Drag the fill handle up to the last row to copy the formula to the remaining cells.

You should now see your Cumulative Frequency column all filled out.

Sample output after filling out the Cumulative Frequency column.

The last cell in this column is the overall accumulated total since the start of the dataset.

 We will use this cell to calculate the Cumulative Percentage.

3. On the fourth column, write this formula: =C2/$C$13

Change $C$13 with the last cell in your Cumulative Frequency. Remember to add anchors or dollar sign ($) in the cell reference so that it always points to this cell.

Add the formula to the first cell of the Cumulative Percentage column.

This formula divides the Current Cumulative Frequency with the Last Cumulative Frequency to get the percentage value.

Once you’ve added the formula, copy it to the remaining cells in the column by dragging the Fill Handle down.

Sample output after dragging the Fill Handle down in the Cumulative Percentage column.

4. As the final step, convert these cells into percentage format.

Select all the numbers in this column. Go to the Home tab and click on the percentage button inside the Number section.

Sample output after converting all cells in the Cumulative Percentage column to percentage format.

And that’s it! You now have your Cumulative Percentage calculated.

Note that the last cell in the column should be 100%, reflecting the percentage of the highest accumulated value. 

2. Using Pivot Table to Calculate Cumulative Percentage

Using Excel’s Pivot Table is probably the quickest way to get the cumulative percentage.

1. Select your entire dataset.

Select the entire dataset.

Note that your dataset should be sorted in the correct order.

2. From the Insert tab, click on the Pivot Table button.

Steps to create a Pivot Table

3. The “PivotTable from table or range” menu will appear.

Select where you intend to add the pivot table – whether on a new worksheet or a specific range in the current worksheet – then click OK to continue.

Select where you intend to add the Pivot Table

4. The PivotTable Field List will appear on the right.

Drag each field to its corresponding areas in the pivot table.

The interval should be in the Rows area, and the value should be in the Values area. 

Drag the Interval field to the Rows area and the Value field to the Values area.

By default, the Pivot Table will get the sum of the value field.

We will change it so that it displays the Cumulative Percentage instead.

5. Right-click on any cell containing the value field in the Pivot Table and select Value Field Settings.

Right-click on any cell in the Value field column and select "Value Field Settings".

6. The Value Field Settings menu will appear.

Steps to convert the value field into cumulative percentage

Change the Custom Name with the appropriate name for the field. You can name it “Cumulative Percentage”.

Go to the Show Values As tab.

In the “Show values as” dropdown menu, select %Running Total In.

In the “Base field”, select the interval.

Once done, click OK.

And that’s it! You now have your cumulative percentage calculated!

What’s great with this method is that if you have new rows added to your dataset, you only need to update the pivot table’s data source and you’re good to go!

Conclusion

Cumulative Percentage has various uses in different industries. No matter what your purpose for it is, I hope this tutorial helps you easily calculate it in Excel. Whichever of the two methods you choose, please remember to always sort your dataset in the correct order before adding the formulas or the pivot table.

How to Insert Arrows in Excel (5 Easy Ways)

Whenever I work on a lengthy report that I need to forward to someone for review, I usually look for ways to highlight sections in the report that require attention.

I used to add cell colors like yellow to do this. But then, some reports already have cell colors in them, so adding more would be too much. That’s when I thought of using arrows instead.

Adding arrows in a report has a lot of use. You can use it to:

  • Immediately shift your audience’s focus to a particular section in the report.
  • Show the data trend, whether it’s going up, down, or steady.
  • Present a flow within the worksheet.

There are plenty of ways to insert arrows in Excel. I’ll go over them one by one.

1. Arrow as a Symbol

With this method, we will add the arrow inside the cell.

1. Select the cell where you would like to add the arrow.

Select the cell where you would like to add the arrow.

2. From the Insert tab, click on Symbols >> Symbol.

Steps to access Symbols in Excel

3. In the Symbol menu, select the arrow you prefer to use and click Insert.

In the Symbol menu, select the arrow you prefer to use and click Insert.

4. And that’s it! You now have an arrow inside your selected cell.

Sample output after inserting an arrow symbol in cell.

You can add text before or after the arrow to mention, for example, an instruction. 

Sample cell with a text and arrow symbol

If you want to add the same arrow to other cells, you can repeat the same steps or simply copy the cell with the arrow and paste it wherever needed.

Aside from arrows, you can also add these symbols in Excel (whichever is applicable):

Diameter Symbol
Sigma Sum Symbol
Statistical Symbols
Square Root Symbol

2. Insert an Arrow using the Wingdings 3 Font

This method is practically the same as the first one, except now, we’re going to use the Wingdings 3 font to insert the arrows.

If you don’t know yet, Wingdings is a font style that only displays symbols. Each time you type a character on your keyboard, the characters are converted into symbols. To insert arrows using Wingdings 3, you only need to remember these four characters:

ArrowCharacter to TypeDescription
(pointing left)!Exclamation point
(pointing right)“Double quotes
(pointing up)#Number sign or hash sign
(pointing down)$Dollar sign

1. On a blank cell, type the character that is equivalent to the arrow you intend to add.

I prefer adding all four of them, one for each cell, so I can easily copy and paste the arrow I need.

Four cells each containing a character that corresponds to an arrow in Wingdings 3

After typing the characters, select all the cells.

2. From the Home tab, go to the Font section and select Wingdings 3.

Steps to change font to Wingdings 3

3. And that’s it! You now have Wingding 3 arrows inside your cells.

Sample output after changing selected cells' font to Wingdings 3

To add more arrows, you could repeat the same steps, or you could simply copy and paste existing cells with arrows.

3. Arrow as a Shape in Excel

Inserting an arrow as a shape is probably everyone’s default go-to approach when asked to add arrows in Excel.

It’s a simple drag-and-drop method, and you are free to change the arrow’s size, color, and other formats.

The only downside of this approach is that if there are changes to the arrangement of the data, you will need to move the arrows one by one to have them point again to the same cells.

So as much as possible, it would be best to have your data format finalized before adding these arrow shapes. Doing so will save you from doing the menial task of dragging the arrows one by one to wherever they should be.

To insert an arrow shape:

1. From the Insert Tab, click on Illustrations >> Shapes.

Steps to add an arrow shape

As you can see, there are a lot of arrow shapes that you can choose.

  • In the Lines section, there are thin arrows — some are in straight lines, while some are curved.
  • In the Block Arrows section, you’ll see thick arrow shapes in varying designs.

2. Click your desired arrow type, then click anywhere on the sheet to add it.

The arrow shape that you have selected should appear.

Sample output after inserting an arrow

Use the dots beside the shape to resize it. Use the revolving arrow on top to shift where you want the arrow to point.

You can change the color of the shape and its borders. Be creative and play around with it. 

Sample output after chaging the format of the arrows

If you’re happy with the arrow and want to create copies of it, you can do so by copying and pasting the shape.

4. Arrow as a Conditional Formatting

If you’re looking for a way to add arrows in a dataset to show whether the numbers meet your targets or not, then this method is for you.

1. Select the group of cells to which you would like to add the arrows.

This group of cells should contain numbers and not text.

Select group of cells to add the arrows to.

2. From the Home tab, go to Conditional Formatting >> Icon Sets.

Steps to add arrows on cells using Conditional Formatting

Select your preferred arrow from the Directional section.

Notice that you have the option to add 3, 4, or 5 arrows.

The number of arrows corresponds to the number of conditions you can add as the basis for these arrows.

3. Once you have selected the arrow type you would like to use, you will immediately see the arrows added inside the cells.

Sample output after adding arrows in cells as Icon Sets (Conditional Formatting)

Next, we will configure the default conditions set for these arrows.

4. While the same cells are selected, go to Conditional Formatting >> Manage Rules.

5. The Conditional Formatting Rules Manager will appear.

From the list of rules, select the one that we just added and click Edit Rule.

Steps to edit recently added Conditional Formatting

6. The Edit Formatting Rule menu will appear.

The "Edit Formatting Rule" menu showing the default rules applied for an Icon Set

There you will see the default rules applied. Feel free to update the Value or the Type fields. Make the necessary adjustments to the rules so that each type of arrow will appear based on the number range you specify.

Once done, click OK. And that’s it! You’re all set.

This conditional formatting can also be applied to another cell if you want or the same formatting is needed.

5. Insert Arrows in a Column Chart

This method is for those who want to change the columns in their charts into arrows.

1. Set up your column chart.

Set up the column chart.

2. Insert an arrow shape and format it however you like.

I suggest you choose from the block arrows — the ones with thicker arrow shapes so you can easily see them in the charts. 

Sample arrow shapes

In my example above, note that I have added two arrows in different colors. You can do the same if you plan to have multiple arrows in your chart.

3. Select the arrow you want to add to the chart and press CTRL + C to copy.

Select the arrow shape that you intend to add in the column chart

4. Next, go to the chart and select the data series inside the chart where you would like to add the arrow.

Select all the data series if you want to change all columns with the same arrow. 

How the chart looks like after selecting all data series

Select the data series one-by-one if you want alternating arrows within the chart.

How the chart looks like after selecting one data series

5. Once you have selected the desired columns to change into arrows, press CTRL + V to paste.

Sample output after pasting the arrow to all data series in column chart

And that’s it! The columns in your chart should now be converted into arrows.

If you want to add a different arrow, repeat steps 3 to 5, but this time, select one data series at a time.

Sample column chart with varying arrows

If you want to, you can adjust the gap width to increase the size of the arrows. And that’s it!

Conclusion

Arrows have a lot of uses in Excel. It can be used to shift your readers’ attention to a particular section in the report. It can also help your readers understand how your numbers are compared to the target set – whether they went up, down, or steady. It can also be used to show the flow within your sheet.

No matter what your purpose for the arrow is, I hope the methods described above can help you achieve what you’re trying to do in your workbook.

How to Create a Custom Autofill List in Excel?

When writing a report in Excel, most of us have this particular list that we regularly add in a specific order (e.g., a list of departments, regions, or stores).

Typing them once is fine. But doing it every single time is a different story. It can become pretty annoying, especially if you have a long list.

To escape the need to type them every time, most of us would probably resort to placing this list in a separate workbook and copying them each time we need them.

But then I discovered a more efficient way: create a custom autofill list in Excel.

Once you have set it up, whenever you need to add the list, you only need to type one item in the list and drag the Fill Handle down. Excel will then fill in the rest of the items for you.

No need to copy the list from another workbook. Pretty interesting, right?

Steps to Create a Custom Autofill List

1. If you have your list typed on a workbook, open that workbook, and select all the items in that list.

Select all items in your list

If you don’t have the list typed out yet, open a new workbook and start typing them there. Once done, select all the items.

IMPORTANT: The list of items should only be in one column.

2. Go to the File menu and select More >> Options.

Go to the File menu and select More >> Options.

3. The Excel Options menu will appear. Click on Advanced.

Scroll down to the bottom part of the menu. Then, click on the Edit Custom Lists button.

Steps to access the "Edit Custom Lists" button

4. Once you click on that button, you will see the Custom Lists menu.

"Custom Lists" menu with default lists added.

Notice that there are already default lists added beforehand.

I actually didn’t know that these lists exist. Had I known, I wouldn’t have manually added the weekdays (Sunday to Monday) or the months in a year (January to December) in my previous reports.

It would have saved me a lot of time if only I typed one item in the list and let Excel fill in the rest. Well, at least we learn something new here.

Now, going back to our new list…

To create an autofill list based on the cells that we previously selected, click on the Import button.

From the "Custom Lists" menu, click on the Import button.

You’ll then see your list on the Custom Lists. Click OK to continue.

Sample of what happens once list is imported.

And that’s it! You now have your custom autofill list that you can access anytime.

IMPORTANT: Note that the list is not linked to the cells that you have selected. So, if you need to change some of the items in the list, you can do so by either:

  • Opening the Custom Lists menu and updating the items included in the List entries (you can type directly on that list box); or
  • Redoing the same steps as above. Don’t forget to remove the list you previously added by clicking on the Delete button so that there won’t be a duplicate list.

Steps to Use a Custom Autofill List

Now that you have successfully added your very own autofill list, it’s time to use it.

To do this:

1. On one cell, type the first item in that list.

On one cell, type the first item in the list.

Note: You can actually start with any item in the list. However, if you intend to have the same order of items, you need to start with the first one.

2. Next, click on that same cell.

3. Hover your mouse on the right side of that cell until you see your mouse cursor turn into a black cross (or a plus sign).

Hover mouse on the right side of the cell to see the Fill Handle.

That is Excel’s Fill Handle. By its name, we would know that it’s a handle we use to control up to where we want the autofill to be applied.

4. Drag the Fill Handle down until you reach the last cell for your list.

If you are not that sure how far down you should drag the fill handle, don’t worry!

As you drag the Fill Handle down, you’ll notice the item to be added to the current cell will appear.

As you drag the Fill Handle down, you’ll notice the item to be added to the current cell will appear.

Drag the mouse further until you see the last item on your list.

Once you see that, release your mouse.

5. And that’s it! You should now have your complete list added to the worksheet.

Sample output after dragging the Fill Handle.

The autofill list is saved in the Excel app. You can access it at any time, even on new workbooks.

If you’re on a different computer though, you’ll have to set up the list again. But as you can see, it’s not much of a big deal as it’s easy to set up.

Sort Records based on the Custom Autofill List

I would assume that your list is specifically arranged based on a particular order.

It could be, for example, sorted by the time when the stores were opened — with the pioneering stores added on top. 

When working on our reports, we often sort our records based on the numbers to see which are performing better and which are not (based on particular scales).

Sample dataset with records sorted based on numbers.

After sorting them that way, we then, at times, would want to sort them back based on the custom list we have created.

Now, our custom list is not necessarily in alphabetical order, so you might resort to adding another field where you would specify the order of the items. But you don’t have to do this anymore if you have a custom autofill list.

You only need to:

1. Select the entire dataset.

Select the entire dataset.

2. Click on the Data tab and click on the Sort button.

Steps to open the Sort menu.

3. In the Sort menu, select the field that contains the custom autofill list.

Steps to sort selected records by custom autofill list.

In the Order dropdown, select Custom List.

In the Custom Lists menu, select the list you have created and click OK.

Select your list in the Custom Lists menu and click OK.

You will then be redirected back to the Sort menu where you will see the items in your list in the Order field. Once you see it, click OK.

The selected list will appear in the Order field of the Sort menu.

And that’s it! You now have sorted your dataset based on your custom autofill list.

Sample output after sorting dataset based on custom list.

Conclusion

Who knew that having your custom autofill list in Excel can save you much time in preparing reports? It only takes you a few minutes to set up, and you’re good to go. You can readily add it to any workbook that you’re working on.

Also, you can use it to sort your dataset based on the order of items in this list – which is probably my favorite thing about it. I hope this article helps!

How to Copy Row Height in Excel (4 Quick Ways)

Copying column widths is easy to do in Excel.

We only need to:

  1. Select the columns to copy and press CTRL + C.
  2. Highlight the columns where we’d like to paste the column widths.
  3. Press CTRL + ALT + V to open the “Paste Special” menu. 
  4. From the Paste Options, select “Column width” and click OK.

And that’s it! We are good to go. Easy-peasy, right?

Excel, unfortunately, doesn’t have a similar feature for copying row heights. But don’t worry! In this article, I’ll show you four methods to copy row height in Excel.

Each of them has its own merits and demerits, so pick whichever is best for your needs.

#1: Copy row height manually

This method is probably the most straightforward approach. The goal is to get the row height of the rows you want to copy and apply that to your selected rows. That’s it.

To do this:

1. Select the entire row that you would like to copy. You can do this by clicking on the corresponding row number at the left.

Select the entire row to copy.

2. Go to the Home tab, click the Format button, and select Row Height.

Steps to view Row Height from the Home tab

3. The Row Height menu will appear.

Row height of selected row will appear in the textbox of the Row Height menu.

Notice that the row height inside the textbox is highlighted. Right-click on it and select Copy.

Right-click on the row height textbox an select Copy.

IMPORTANT: If the row height is blank, it means you have selected multiple rows with varying row heights. Repeat the first step and make sure to only select one row.

4. Once you have copied the row height, close the Row Height menu.

5. Next, select the row(s) you would like to paste the row height.

Select the row(s) you would like to paste the row height.

To paste it on multiple rows, click on the first row. Then, while holding the SHIFT key, click on the last row.

6. Once selected, right-click on one of the selected rows. In the list of options, click Row Height.

Right-click on one of the selected rows and select Row Height.

Note that this is another way to open the Row Height menu.

7. Once the Row Height menu appears, paste the row height we copied earlier.

You can do this by deleting the existing row height, if any, and right-clicking on the textbox and selecting Paste.

Right-click on the Row Height textbox and select Paste.

And that’s it! You should now have the same row height for the rows that you have selected.

Sample output after manually copying row height.

#2: Copy row height with a keyboard shortcut

This method is practically the same as the first one. The only difference is that we will do the steps using keyboard shortcuts. If you have a lot of row heights to change, this will help you do the update fast.

1. Select a cell from the row that you’d like to copy.

Select a cell from the row that you intend to copy the row height.

2. Press ALT + H + O + H.

The row height of selected cell will appear in the textbox of the Row Height menu.

The row height menu will appear. Take note of the row height that appears (or copy it if that’s easier).

3. Close the Row Height menu.

4. Now, select the cells (or the rows) where you would like to apply the same row height.

Select the cells where you would like to apply the same row height.

5. Press ALT + H + O + H again.

In the row height menu, type the desired row height (or paste it if you copied it earlier) and click OK.

In the row height menu, type the desired row height (or paste it if you copied it earlier) and click OK.

6. And that’s it! You have the row heights adjusted in your selected cells.

Sample output after using the keyboard shortcut method to copy the row height.

#3: Copy row height using Format Painter

If your goal is to copy multiple rows with varying row heights at one time (e.g., 20, 14, 10, 13), then you might prefer the last two methods that I’m about to show you.

The only disadvantage of both these methods is that you will be copying not only the row height but also the format of the cells. If this is not an issue for you, then please read on so you can try it out.

1. Select the rows that you would like to copy.

Select the rows to copy.

2. Go to the Home tab and press on the Format Painter button.

Steps to activate the Format Painter from the Home tab.

If you prefer a keyboard shortcut, you can press ALT + H + F + P.

You’ll know that the Format Painter is activated once you see the broken lines surrounding the rows.

3. Next, select the rows where you would like to paste the row height.

IMPORTANT: Click and drag the mouse on the row numbers (not on the cells).

Select the rows where you would like to paste the row height.

Once you release the mouse, the format of the previous cells will be applied (along with the row height).

Sample output after using the Format Painter.

And that’s it! Feel free to change the format of the cells within the selected rows.

#4: Copy row height by copying the entire row

Format Painter can be tricky to use because once you’ve activated it, you need to make sure that all the rows where you would like the format to be pasted on are all selected at once.

Otherwise, you’ll have to redo the steps, which can be really frustrating.

Personally, I don’t like Format Painter that much because I tend to miss some of the rows, so I always end up repeating the steps.

So, as an alternative, I would instead copy the rows, paste them wherever necessary, and then remove the values after that. If this sounds good to you, then please read on.

Here’s the step-by-step guide:

1. Select all the rows that you would like to copy.

Do this by clicking on the first row, and while pressing the SHIFT key, click on the last row.

Select all the rows to copy.

2. Press CTRL + C to copy.

3. Select all the rows where you would like to paste the row height.

You can take your time here. The rows are still in “copy mode” even if you are selecting different rows.

Select all the row heights to paste the row height to.

4. Once you’re happy with the selected rows, press CTRL + V to paste.

Sample initial output after copying and pasting entire row(s).

5. Then, while the cells are still selected, press DEL to delete the values.

While cells are selected, press DEL to delete current values.

6. If you also need to change the format, then please go ahead.

And that’s it! You have the same row heights applied to the selected cells.

Conclusion

Even though Excel doesn’t offer the “copy row height” feature, there are still ways for you to implement it. I hope the methods listed above are of great help to you.