Without doubt, an Excel spreadsheet is one of the most advanced tools for working with raw data—and one of the most feared. The application looks complicated, way too advanced, and like something that would take hours to figure out.
I wouldn't be surprised if upon hearing that you had to start using MS Excel, your heart started to pound. Is there any way to make Microsoft Excel less scary and intimidating? Yes.
By learning a few spreadsheet tricks, you can bring Excel down to your level and start looking at the application in a different light. We rounded up some of the simplest yet powerful MS Excel spreadsheet tips you can start using on your data.
- USE MS EXCEL FORMAT PAINTER
To start you off, get yourself familiar with formatting your spreadsheet cells. A visually organized spreadsheet is highly appreciated by others as it can help them follow your data and calculations easily. To quickly apply your formatting across hundreds of cells, use the Format Painter:
Select the cell with the formatting you wish to replicate
Go to the Home menu and click on the Format Painter. Excel will display a paintbrush next to the cursor.
Using Excel Format PainterWhile that paintbrush is visible, click to apply all of the attributes from that cell to any other.
To format a range of cells, double-click the Format Painter during step 1. This will keep the formatting active indefinitely. Use the ESC button to deactivate it when you're done.
- SELECT ENTIRE SPREADSHEET COLUMNS OR ROWS
Another quick tip– use the CTRL and SHIFT buttons to select entire rows and columns.
Click on the first cell of the data sequence you want to select.
Hold down CTRL + SHIFT
Then use the arrow keys to get all the data either above, below or adjacent to the cell you're in.
You can also use CTRL + SHIFT + * to select your entire data set.
- ENTER THE SAME DATA INTO MULTIPLE CELLS
At one point, you may find yourself needing to enter the same data into a number of different cells. Your natural instinct would be to copy-paste over and over again. But there's a quicker way:
Select all the cells where you need the same data filled in (use CTRL + click to select individual cells that are spread across the worksheet)
In the very last cell you select, type in your data
Use CTRL+ENTER. The data will be filled in for each cell you selected.
- DISPLAY EXCEL SPREADSHEET FORMULAS
Viewing Spreadsheet Formulas
Jumping into a spreadsheet created by someone else? Don't worry. You can easily orient yourself and find out which formulas were used. To do this, use the Show Formulas button. Or you can use CTRL + ` on your keyboard. This will give you a view of all formulas used in the workbook.
- FREEZE EXCEL ROWS AND COLUMNS
This is a personal favourite of mine when it comes to viewing lengthy spreadsheets. Once you scroll past the first 20 rows, the first row with the column labels annoyingly disappear from view and you begin to lose track of how the data was organized.
Freezing Excel Columns Rows
To keep them visible, use the Freeze Panes feature under the View menu. You can opt to freeze the top row or, if you have a spreadsheet with numerous columns, you can opt to freeze the first column.
- COPY FORMULAS OR DATA BETWEEN WORKSHEETS
Another helpful tip to know is how to copy formulas and data to a separate worksheet. This is handy when you're dealing with data that's spread across different worksheets and requires repetitive calculations.
With the worksheet containing the formula or data you wish to copy opened, CTRL + click on the tab of the worksheet you want to copy it to.
Click on or navigate to the cell with the formula or data you need (in the opened worksheet).
Press F2 to activate the cell.
Press Enter. This will re-enter the formula or data, and it will also enter it into the same corresponding cell in the other selected worksheet as well.
These general tips won't turn you into an Excel guru overnight. But they can help you take that first step towards becoming one! Are you a well-seasoned Excel user? Which of your spreadsheet tricks would you add?