10 Excel Tricks Everyone Should Know

10 Excel Tricks Everyone Should Know

1. Copy/Paste to Multiple Cells Quickly  
Need to copy the content of one cell into multiple other cells below? Instead of copying and pasting the content one by one, you can just highlight the cell and the cells below and press CTRL+D. The content will automatically be copied below. This works for formulas too. 

Select the cell you want to copy.

Highlight the cells below and press CTRL+D

Another way you can copy to multiple cells is by clicking the tiny square in the bottom right of a highlighted cell and dragging it to the cells where you want to copy the content. If you double-click the square, the content will be copied to the cells below, as long as there is a column next to it with content in it. The copying will stop when the first empty cell in the neighboring column appears. 

You can also double-click the little green square to paste the content to the cells below. 

How: CTRL+D

 

2. Quickly Delete Columns and Rows
If you need to delete columns or rows, you can right-click on the column or row and select Delete. A faster approach is by highlighting the columns or rows you want to delete and press CTRL plus the minus key.

Highlight the cells you want to delete and clikc CTRL+[Minus Key]

How: CTRL+[Minus Key]

 

3. Jump to the end of a spreadsheet
Navigating a big spreadsheet can be tricky, especially if it contains thousand of rows or columns. You can use the scroll bar to navigate, but if you need to jump to the last row or column there is a better way. Holding down the command key and pressing one of the arrow keys will cause the selected cell to jump to the top, bottom, left, or right of your spreadsheet. The highlighted cell will stop once it meets an empty cell, which means you can also use this trick to jump to the first empty cell in a column—useful if you are looking for missing data. 

How: CTRL+[Arrow Key]
(If you are on a Mac, use Command instead of CTRL)

 

4. Convert formulas into values
Sometimes you create a formula in your spreadsheet and want to make sure your data doesn’t change. You can turn the results of the formulas into ordinary numbers or text by using the Paste Special function in Excel. Simply highlight what you are looking to convert, go to Edit in the Excel menu at the top of your screen, and select Paste Special. A new window will open with various options. Now, select Values and press OK.

Paste Special

Paste Special

Paste values.

How:
Select cell , copy ,
go to Edit > Select Paste Special > Select Values
Press OK

 

5. Double-click for column width: adjust columns in a flash. 
Tired of resizing the width of your columns to fit the content? Simply double-click at the edge of a column head and it will automatically snap to the width of the content in the column. You can even highlight a number of columns and double-click between two column headers and all of the highlighted columns will automatically resize. 

Long text in a cell can easily be fixed. 

Double-click on the line between columns to have them fit to the width of the text.

How:  
Place the cursor between two columns, double-click 

 

6. Transpose (Change the direction of your list)
Every now and then you'll find a horizontal list across a column that you wish was a vertical list in a single column (or vice versa). The Transpose function under Paste Special allows you to do that. Simply copy the cells you want to transpose, select the first cell where you want the content to go, go to Edit > Paste Special, Select Transpose and click OK. The cells will convert to opposite direction. 

Highlight the text you want to transpose and click copy.

Select the destination for the text. 

Select Transpose, Click OK. 

Now you just need to delete the row above and you are done. 

Now you just need to delete the row above and you are done. 

How:
Edit > Paste Special, Select Transpose, click OK.

 

7. Freeze Panes
When you work with spreadsheets that are larger than your display, the Freeze Pane function can help you keep track of headers or columns by “freezing” them to the top or the left of your screen. The content will then flow underneath the frozen rows or columns as you scroll. To freeze a row, highlight the row underneath the row you want to freeze, go to Window, and select Freeze Panes. This feature is also how you remove the frozen state.

Make sure to select the row below the row you want to freeze. 

Go to window > Freeze Panes

As you scroll down, you will notice the rows flow underneath the top row, making it easy to keep track of what the columns contain.  

 

8. Fit text into the cell no matter the length
If you write a lot of text in a cell, it will automatically continue as one long line of text. This can make it difficult to read. There are two ways to make text fit into a cell.

Wrap text or make line breaks when you have a long string of text. 

The first way is by wrapping the text. Highlight your cell and click the Wrap button in your Excel menu.

This is what wrapped text looks like. 

The second way is by creating a line break within a cell. To create a line break, place the cursor where you would like the line break to be and press ALT+Enter.

This is what line breaks in a cell looks like. 

This is what line breaks in a cell looks like. 

How: ALT + Enter

 

9. Protection
If you share spreadsheets with colleagues but don’t want them to change the content, the Protection function in Excel can be helpful. The Protection function locks all cells by default, so nobody can enter or alter any information. 

To turn on Protection, go to Tools > Protection. 

In the same window, you also have the option of turning on certain functionality while keeping others locked. Another nice feature is the ability to add a password. Only people who know the password can turn off the protection and make edits. However, be careful with password protection, as you can risk locking yourself out of the spreadsheet if you forget the password (unlike your Facebook account, for example, you can’t just reset your Excel spreadsheet password). Instead, we recommend just turning on the protection without adding a password. This feature will give people warning if they try to edit the spreadsheet. They can still turn off the protection, if they know what to look for—but luckily, most people don't.

Go to Tools > Protection > Protect Sheet

Add a password or leave it blank, click OK. 

How: Go to Tools > Protection > Protect Sheet

 

10. Validation
The validation function in Excel can help make sure that data entered is submitted in the right format. Validation basically helps guide users to enter the right information in the right format, so you avoid having to clean up the data afterwards.

Validation works on a cell by cell basis. You can select one or several cells at a time and turn on validation for those cells, whether one or one hundred. 

Some of the ways you can utilize validation is by checking that it is a number is entered. For example, if you want a phone number entered, you can ensure that it is 10 digits long. You can even create dropdown menus with predefined categories.  

Go to Data > Validation. 

Go to Data > Validation. 

Select the function in the dropdown menu and follow the instructions. 

Select the function in the dropdown menu and follow the instructions. 

How: Go to Data > Validation

 

What did we miss?
Do you have a favorite trick you want to share? Let us know in the comments.