Microsoft Excel is one of the most popular spreadsheet applications for processing data quickly. But for a beginner, the process of creating Excel spreadsheets, completing formulas and functions can be daunting.
But don't worry, here are 15 important and useful Microsoft Excel tips and tricks, both for beginners and those who are already advanced, so that they can help you get the most out of it.
15 Important and Useful Microsoft Excel Tips and Tricks
1. One Click to Select All
You may know how to select all by using the shortcut Ctrl + A, but few know that with just one click of the corner button, as shown in the screenshot below, all data will be selected in seconds.
2. Switching Between Different Excel Files
When you open different spreadsheets, moving between different files is very annoying because sometimes working on the wrong sheet can ruin the whole project. Using Ctrl + Tab makes it easy to switch between different files freely.
3. Add Diagonal Lines to Cells
When creating a classmate address list, for example, you might need a diagonal link in the first cell to separate the different row and column attributes. How to make?
Everyone knows that Home->Font->Borders can change different borders for cells, and even add different colors. But if you click More Borders, you will get more surprises, such as diagonal lines.
4. Add More Than One New Row or Column
You may know how to add a new row or column, but it really is a waste of time to insert more than one by repeating this action X times.
It is best to drag and select X rows or columns (X is two or more) if you want to add X rows or columns above or to the left. Right-click the highlighted row or column and select Insert from the drag-down menu. The new row will be inserted above the row or to the left of the column you first selected.
5. Move and Copy Data Quickly in Cell
If you want to move a column of data in a spreadsheet, a quick way is to select it and move the pointer over the border, once it changes to a cross arrow icon, drag to move the column freely. What if you want to copy data? You can hold down the Ctrl key before dragging to move; the new column will copy all the selected data.
6. Delete Blank Cells Quickly
Some default data will be blank, for various reasons. If you need to clear this to maintain accuracy, especially when calculating average values, a quick way is to filter all blank cells and delete them with one click.
Select the column you want to filter, go to Data->Filter, once the bottom button appears, deselect Select All then choose the last option, (Blanks). All blank cells will be displayed immediately. Go back to Home and click Delete directly.
7. Quick Navigate with Ctrl + Arrow Keys
When you click Ctrl + any arrow key on your keyboard, you can jump to the edge of the sheet in different directions. If you want to jump to the bottom line of the data, just try clicking Ctrl + down button.
8. Moving Data from Row to Column
You will use this feature if you want to transpose data to get a better view; however, retyping all the data would be a burden. But there is an easy way, just need to copy the area you want to transpose, move the pointer to another empty location. Go to Home->Paste->Transpose, please note that this function will not activate until you copy the data first.
9. Hide Data
Almost all users know how to hide data by right-clicking to select the Hide function, but this can be easily noticed if there is very little data.
The best and easiest way to completely hide data is to use the Format Cells function. Select the area and go to Home->Font->Open Format Cells->Number Tab->Custom->type ;;; -> Click OK, then all values in that area will not be visible, and can only be found in the preview area next to the Function button.
10. Starting with 0
When input values start with zero, Excel will remove zeros by default. Instead of resetting Format Cells, this problem can be easily solved by adding single quotes in front of the first zero. For example as follows '08123456789.
11. Rename Sheet Using Double Click
There are several ways to rename a sheet, and most users will right-click to select Rename, which is a waste of time. The best way is to double click it, then you can rename it right away.
12. Setting the print area on the sheet
To set the print area, highlight the cells you want to print. In Page Layout, click the drop-down below Print Area and select Set Print Area.
13. Create simple graphics
Graphs are a great way to summarize and simplify the presentation of your data in excel. You can easily graph the values of a given data set to communicate summary projections to your audience.
To easily visualize data and present it in an easy way to users, highlight the data you need to summarize. Go to the Insert Tab > select a simple graphic to use.
14. Sort data
Sorting data in a large excel worksheet can be tedious and time consuming. Luckily enough, Excel makes this easier by offering its users a sorting feature that can be easily accessed from the main menu. To do so, go to the Data Tab followed by the Sort & Filter option. Then just press Sort.
15. Save file
It is important to save the finished file for reference and editing if needed. You must save your file first before you can share it. Here are the steps to follow while saving the file.
Click the File option at the top.
Click Save As if you are saving the file for the first time.
Under Save As, select a location where you want to save the file. For example, you can save on your desktop or a single drive.
Type a file name in the File name box.
You can also save the file in a different format, such as .xls or .txt. Click on the Save As Type list to select a file format.
Finally, click Save.