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.
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.
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.
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.
Select cell , copy ,
go to Edit > Select Paste Special > Select Values
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.
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.
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.
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.
The first way is by wrapping the text. Highlight your cell and click the Wrap button in your Excel menu.
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.
How: ALT + Enter
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.
How: Go to Tools > Protection > Protect Sheet
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.
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.