Excel is one of the best tools to use whenever you want to analyze big data. It has tons of nifty functions to help you manipulate your data efficiently. However, using Excel is not as easy it might seem. There are a lot of functions that you need to know and it’s easy to get lost.
Here are 13 tips and tricks that will help you master Excel in no time.
Format different sheets at the same time
Have you ever worked on an Excel file with different sheets that had the same formatting? Instead of having to go to each sheet and format it, you can make use of Excel’s grouped worksheet feature.
To activate this feature, click on the tabs of the sheets you want to format while pressing Ctrl. The tabs will change color to white. This means the formatting you put on the active sheet is ready to replicate onto the other sheets as well.
Shifting between different Excel files
If you are working on two Excel files and have to constantly move from one to the other, there’s a chance you’ll make mistakes along the way. You cannot afford this, especially if you’re dealing with large data.
To prevent it, simply shift between different Excel files by pressing Ctrl + Tab. This trick will save you a lot of time.
Inserting rows and columns
An Excel sheet gives you the ability to insert rows and columns easily. You do this by placing the cursor on the row/column you want the new row/column to come before. Right-click, and select Insert, or click Ctrl and + (Ctrl and Shift and + in MacOS), or click the Insert ribbon and Rows or Columns.
Transferring Excel data to Word or PowerPoint
At times, you might need to transfer your Excel data into a Word report document, or a PowerPoint presentation. You will have to copy and paste the data, but if you use the common Ctrl + C and Ctrl + V, you’ll end up with a badly formatted table in your Word or PowerPoint.
Get around this by copying the data you need, and right-clicking on the point where you want to paste it. Under paste options, you’ll find many options you can choose from.
Deleting blank cells
Blank cells can lead to distorted values, especially when you’re calculating the average. To avoid this, delete the blank cells by using the filter function. Click on the column, go to Data -> Filter. A downward button will appear next to the column. Undo ‘select all’, scroll down, select blanks and then click OK. All blank cells will show up, so you can select them and press delete.
Make sure to turn off the filter button for returning to your previous data. This is a great skill for your next Excel project.
This is a good tool to use, especially when you are dealing with repetitive data or sequential data like dates. Just input the first value in the cell, then move the cursor to the bottom right corner of the cell until a + sign emerges. Drag the sign downwards to the final cell you would like to fill.
Hide data from other users
There are two ways of hiding data in Excel. You can either right-click on the data that you want to hide, then select Hide. You can also go to Format and under ‘visibility’, select Hide & Unhide and choose whether you want to hide the row, column or sheet. The disadvantage of this method is that it’s easy to notice when you are dealing with a sheet having little data.
A better way of hiding data is by using the Format Cells function. Go to Format -> Format Cells, click on Custom and then type; in the Type field. Click OK and all the values will be invisible (you’ll only be able to see them in the preview area next to the function button).
Make use of simple calculations
There are tons of formulas you can use when sorting out your data. If the formula you need can be typed out in a simple way by making use of addition +, subtraction -, multiplication *, and division /, then do it. This will save you a lot of time and energy in finding the perfect formula.
Turning rows into columns or vice versa
There are times when you might want to convert your column data into rows, or vice versa. This doesn’t mean you will have to retype the whole sheet again. You can just copy the block of data you want to convert, and right click on the cell you want to paste it in. Go to Paste special, then select transpose and click OK. This will preserve your Excel sheet’s quality.
Combining cells through &
This trick is great for people who want to compose data from different cells into one cell. The “&” sign will be handy for you here. Just click on the cell that will hold the combined data and enter the equal sign, followed by the cells you want to combine, separated by “&”. For example, if you want to combine the data in cells A1 and B1 just use “=A1&A2” as the formula.
Inputting values with zero
Have you ever tried to input a value that starts with zero in an Excel cell? The program usually deletes the zero by default - but sometimes, you really need it to be there. The best way to get around this is by putting a single quote mark before zero when typing the number e.g., - 0005782. The zeros will not be deleted when you click enter.
You can get tons of Excel jobs that require this kind of formatting.
Navigating through an Excel file like a pro
Are you dealing with a large worksheet and you don’t want to use the mouse? Here are some keyboard shortcuts that can help you. If you want to select cells easily, just hold down Ctrl and press the up, down, left or right arrows to select the data all the way to the end of the sheet.
You can move through worksheets by holding down Ctrl and pressing Page Up or Page Down. You can also turn a number into currency by holding down Ctrl + Shift and pressing the dollar sign on your keyboard.
Use Autocorrect like a pro
If you have a particular set of values, like names, which you need to input every single time, you can use Excel’s Autocorrect feature. You can simply go to File -> Options -> Proofing -> Autocorrect options. You can then assign each name a certain initial. You should make sure that the initials are unique to each name or entry, and that you can remember them easily. Close the window and return to your Excel sheet. You can now enter your initial and press enter; Excel will autocorrect it automatically.
Excel is an important tool for anyone who’s intending to analyze a large amount of data. These tips will help you become an Excel pro in no time.
Do you have more tips and tricks that can be used in Excel? Share them below.