14 Cool Excel Tricks

If your study or job is closely connected with Excel, you definitely need to learn some Excel tricks that will facilitate the calculations. When Microsoft developed Excel to the version of 2010, it has brought numerous surprises to its users. Undoubtedly, one cannot underestimate the role of Excel in the everyday life of many people, especially when it comes to dealing with big data and complicated formulas. In many cases, both the beginners and the advanced experts, are not aware of the cool Excel hacks that can greatly facilitate their lives. Therefore, our specialists have gathered a list of Excel tips and tricks that should help you work in Excel more productively and prepare your excel homework without any problems. Keep in mind that all these features can be performed only in Microsoft Excel 2010.

1. Shifting Between Several Excel Files

When the Excel expert has several different spreadsheets open, he/she finds it truly annoying to shift between different files since sometimes working on the incorrect spreadsheet can spoil the entire project. To shift between the different tabs, the user can use Ctrl + Tab. Besides, this feature can be applied for opening the Windows tabs in Firefox using Windows 7.

2. Opening Excel Files in Bulk

If you have many files to open but clicking on each of them seems boring, there is a wonderful option that will facilitate your life. You just need to choose all files you want to open and click Enter on the keyboard. In just one-two seconds, all the necessary files will be opened simultaneously.

3. Use Only One Click to Select All Items

Being an advanced user, you may know that to select all items you need to press Ctrl + A shortcut. It is right. However, not so many people know that clicking on the left corner button, as it is shown in the screenshot below, you will be able to select all data in just 2-3 seconds.

4. Input Complicated Terms with AutoCorrect

If you have to repeat the same value and it is difficult to input, the best way is to utilize the AutoCorrect function, which will help you replace your text with the correct one. Take my name, John Smith, for instance, which can be replaced by JS. Therefore, whenever I input JS, Excel will autocorrect it to John Smith. Select File-> Options-> Proofing-> AutoCorrect and input Replace the initial text with correct one in the red area.

5. Add Two or More New Columns or Rows

If you know how to add one new row or column, you can do it. However, inserting two or more rows or columns can be a rather time-consuming process. Repeating this action many times, you waste your precious time. If you want to insert two or more rows or columns, you just need to highlight X rows or columns and point above or left. Then, press on the highlighted rows or columns and select Insert from the menu that pops up. After that, new rows and columns will be added to the left of the column or above the row you selected initially.

6. Add a Diagonal Line into a Cell

When making some list, you may want to put a diagonal link in the very first cell to divide the unique attributes of the columns and rows. For instance, the first row should contain the names of your classmates and the following columns will include the details of their age, addresses, and dates of birth. How to create this line? Everybody knows that Home->Font-> Borders may change various borders for a cell, and even add various colors. However, clicking More Borders, you will see much more features, and adding the diagonal line is among them. All you need to do is just to click it and press Save.

7. Make a New Shortcut Menu

In general, there are three main shortcuts in the top menu, Save, Repeat Typing, and Undo Typing. If you need to add more shortcuts like Cut or Copy, you need to follow the standard procedure:

Press File-> Options-> Quick Access Toolbar, select Cut and Copy and save the changes. After everything is done correctly, two more shortcuts will be inserted in the menu.

8. Move and Copy-Paste Data in Cells with Great Speed

When you need to move one column to a spreadsheet, the fastest way to do it is to elect it and move the pointer to the border. After it turns to a crossed icon, you can set the column in motion. If you need to copy and paste the data, press the Ctrl button before moving and the new column will copy-paste all the data chosen.

9. How to Delete Blank Cells Fast?

For many reasons, the default data may be blank. If you want to delete blank rows to reach accuracy, especially when it comes to calculating the average value, the fastest way to do it is to filter out all blank cells and remove them with just one click. To do it effortlessly, select the column you need to filter, press the Data->Filter and pick up the very last option, Blanks. It will show you all the blanks right away. Then, return to home and Click Delete to delete all those Blanks.

10. Just One Click to Get Better Status

A vast majority of Excel users know how to see the data status at the very end of the Excel sheet. However, the learning that you can move the pointer to the bottom tab and press to get more status, as it is shown in the example, is very helpful.

11. Generate a Unique Value in a Column

Undoubtedly, if you are an advanced user, you know all the functions of Filter. However, not all people take advantage of the Advanced Filter, which should be widely applied if one has to filter a unique value from data in some column. You have to select the column and go to Data-> Advanced Filter. Then, a pop-up window will appear. According to the screenshot, press Copy to another location, which needs to follow the second red area. After that, feel free to choose the location needed clicking the area-choosing button or by typing the value. In the current image, the unique age can be generated from Column C to the Column E. Make sure to select Unique records only, then click OK. The unique value in column E can serve for the contrast of the initial data in Column C, that is the reason why we highly recommend to copy to a totally different location.

12. Easy and Fast Navigation using the Ctrl + Arrow Button

When pressing Ctrl + any arrow button, you will be able to jump to any of the edges of the sheet. For example, if you need to jump to the final line, just press Ctrl + downward button respectively.

13. Hide Data Carefully

Almost all users are aware of the essentials of hiding data by selecting the Hide function, but it can be noticed if there is only a small data. The easiest way to hide data carefully implies using the Format Cells function. Select the needed area and press Home-> Font-> Open Format Cells-> Number Tab->Custom->Type -> Press OK, which will make all the values invisible. One will be able to find these values in the preview area following the Function button.

14. Input Values that Start with 0

If an input value begins with 0, Microsoft Excel will remove it by default. Instead of resetting the Format Cells, you can easily solve this problem by adding a quote mark ahead of 0, as it is shown on the image.