10 Tricks That Can Make Anyone an Excel Expert 

We all agree that Microsoft Excel is a functional and versatile tool, which can be used in a wide range of work scenarios. Truthfully, it wouldn’t be wrong to argue that Excel is the most powerful tool of the Microsoft Office Suite, and when you know how to use it, you’ll be surprised by its capabilities.

On that note, Excel can be used for the typical sum and average formulas and functions, as well as for comprising more advanced pivot tables or Excel macros. A good starting point would be to take a look at some Excel examples. What is more, if you have a love/hate relationship with Excel, the following tips will definitely help you out.

1. Transfer Data from a Row to a Column

This feature is genuinely useful if you want to transfer data, so that the information makes more sense, or to obtain a better, more logical display. Nevertheless, simply retyping the data is a time-consuming process you’d be better off skipping. So, this feature is worth noting.

Here’s how you do that. You copy the information you wish to transpose. Afterward, you move the pointer to a blank location. After copying the data, you should go to home, paste, and transpose.

2. Create Text with &

Another tip that could diversify your use of Microsoft Excel is composing text by using &. Using complex and complicated formulas isn’t mandatory, considering that you know this trick. As a matter of fact, you can create any text by using this symbol. For starters, you ought to locate the cell that should feature the composed result, and use the location with &, as following: A2&B2&C2&D2.

3. Move and Copy Data in Cells

In the case in which you intend to move one column of data to a separate spreadsheet, there is a quick and efficient way of doing that. Firstly, you should select it, and afterward, move the pointer to the border. As soon as you see the crossed arrow icon, it’s time to draw the column freely where you wish. Nonetheless, what if you plan on copying the data?

In this case, simply press CTRL before dragging to move. That will make the new column incorporate all the selected information.

4. Add Numerous Rows or Columns

If you want to modify the tables you’ve created in your Excel sheet, you should know that there is a way to include numerous rows or columns. So, the best way to save time in this case is by dragging and selecting X rows and columns, if you wish to add those rows and columns to the left, or above. Here’s what you should do: right click the rows and pick select from the drop down menu. As soon as you do that, the additional rows will be inserted where you want them to.

5. Add Values Starting with 0

If you attempt to introduce values, which start with 0, Excel will automatically eliminate the 0, which will modify the final calculations and results you’ll obtain. So, as opposed to resetting the Format Cells, you can solve this issue by including a single quote mark ahead of the zero – and, there you have it, problem solved.

6. Hide Data

If you didn’t know until now, Excel allows you to hide data. The simplest way of doing that is by choosing the Hide function. Nonetheless, if you intend to hide data thoroughly, the way of doing that is by utilizing the Format Cells function. On that note, you should pick the spot you want and go to home, font, open format cells, number tab, custom and type. Once you click ok, the values in that given area will be hidden. You’ll be able to find them in the preview area only, in the proximity of the Function button.

7. Quickly Delete Blank Cells

Due to various reasons, in some cases, some default data might be blank. So, if you wish to delete these in order to preserve the accuracy of the info, there is a quick way of getting rid of blank cells. Start with picking the column you wish to filter. Next, go to Data, Filter – as the downward button appears, you can choose select all, and pick the last option – blanks. Right afterward, you’ll see all the blank cells. Return to home and choose delete directly to have the blank cells removed.

8. Open More Excel Files at Once

Let’s say that you wish to work on multiple Excel files at once. This is a possibility, as you can open all files at once, with one click. What you have to do is select the files you want to open, and press enter. It’s as simple as that.

9. Transform the Case of the Text

Although using formulations may seem like a foreign language to an inexperienced excel user, this will convince you that it doesn’t necessarily have to be this way. For instance, there are plentiful simple formulations you can find useful. An example would be the upper, lower, and proper, which you can use to transform texts for distinct motives. Now, let’s see what each formulation does. Firstly, you can use upper to capitalize all characters – e.g. UPPER(A2). Secondly, using lower will change the text to all lower cases, whereas proper will capitalize the first letter of the word.

10.Fast Navigation

This tip is quite simple, but that doesn’t make it less useful, in any way. Therefore, you can click CTRL combined with an arrow button on the keyword, so that you can reach the edge of the sheet, where you need. For instance, if you want to jump to the bottom line of the sheet, simply click CTRL and downward button.

Although using Microsoft Excel might seem overwhelming to first-time users, don’t get discouraged. Practice makes perfect – and by being consistent and eager to learn, you’ll become an expert in no time. The tricks included in this article are just some of the many great ways in which you can maximize your productivity as you use Excel.

Shruti Gupta is a writer, marketer and outreaching expert .She writes about technology, startups & other niches. She has contributed to a number of famous websites like Thenextweb, Deccanchronicle and Crazyegg. Stay tuned with her at:@shruti_gupta01 or via Skype : shrutigupta2811