Proven Tips To Become An Excel Expert

Posted on - Last Modified on

There are people who love Microsoft Excel, and those who hate it. For many, it is a daunting program. It offers countless features, so mastering Excel is not something you can achieve overnight. In fact, there are few people who know this application inside out. You may come across other options, but Microsoft Excel is still the best program for creating and handling spreadsheets.

There is more to Excel than just formatting, and functions to perform calculations. Spreadsheets are an absolute must in a number of industries, and every business that uses Excel requires an Excel master. Whether you are a beginner or an advanced user, here are a few excellent tips that will help you master the application, and boost your productivity.

Select All

To ‘select all’, just click first and then drag to select a particular grid of cells. If you want to choose all the cells, use the keyboard shortcut Ctrl+A (Windows 10) or Command + A (MacOS). Another simple trick most people are unaware of is to click the reduced cell noticeable by a white arrow, seen in the top left corner.

Shading Every Other Row

Spreadsheets can become awfully boring, especially when you are handling tons of data. A neat way to make your data legible and interesting to read is to apply color to each of the rows.

If you wish to apply color to the whole spreadsheet, simply select all. Otherwise, start by highlighting the area you want to change the color of, and then follow these steps:

  • Click the Conditional Formatting button under the Home tab

  • Select New Rule from the subsequent drop-down menu

  • Choose Classic in the Style drop-down menu

  • Select ‘Use a Formula’ for determining the cells to format

  • Enter the formula “=MOD(ROW(),2).”

  • Choose the color you want to apply, and click OK

Once you complete these steps, you can see that the rows appear in different colors.

Hide or Unhide Rows

There are times you may want to show important data, and hide the not so important stuff. This is where the hide and unhide options offered by Excel comes in handy.

To hide a row or column in Excel, simply right-click it, then select Hide. Once hidden, a thick border appears within end-to-end rows or columns.

In order to unhide a certain column or row, first highlight those on either side of it. Then right-click and choose the Unhide option from the subsequent list.

To unhide several rows/columns, select the ones under consideration, right-click and then select Unhide. An important point to keep in mind is that you should unhide one axis any single time in order to unhide both rows and columns.

It is not hard to locate an Excel expert near you. Visit freelancer.com to find what you’re looking for.

How to Use the VLOOKUP Function

Excel's VLOOKUP function is helpful when you want to recover data from a specific cell; say, for example, checking the value of a specific product from a list of products.

Let's use this example and see how VLOOKUP function helps us to find the price of an item. The data available in the spreadsheet are Item Number, Product Name, and Price. By using the function, you should be able to enter an item number and arrive at the price instantly.

Before applying the function, you should identify two cells – one to input data, and the other to hold the output. In this example, let's choose cell I3 to input data and I4 to write the VLOOKUP function. In the function, you need to specify the pertinent data is available B2 to D11 and the answers are available in the third column.

The formula goes like this: “=VLOOKUP (input cell, the range of relevant data, columns containing the answers).” So, after applying the column numbers, you get: “=VLOOKUP(I3,B2:D11,3).”

Creating a Drop-Down List

A great way to limit the options to a specific set of items is to use a drop-down list. Not only does it improve your data entry efficiency, it also helps users pick from a predefined list rather than typing in data.

To create a drop-down list in Excel, choose a cell, go to the Data tab, and select Validate.

Under the Settings tab, you can find a drop-down menu named Allow. From that menu, select List.

Highlight the cells where you want the drop-down list to appear, and click OK.

When the users click this particular cell, they get a drop-down list from which they can easily choose their option, without having to type in the data.

There are also additional tips for protecting your excel with password and drawing graphs.

How to Display Leading Zeroes

Sometimes you have to enter numbers in Excel that begin with one or more zeroes, for example, zip codes. When displaying these numbers, Excel, by default, has the habit of hiding the leading zeroes. This problem can easily be fixed by a single quotation that is placed before the zeroes, like this - “‘00001.”

How to Use the Concatenate Function

Most of the time, the data in your Excel spreadsheet is not organized the way you want it to be. In some cases, you can combine data from two or more cells and add it to a single column. This can easily be done in Excel with the help of the Concatenate function.

Suppose there is a spreadsheet containing both the first and last names and designations of employees in a company. In order to merge all this information together, we write: “=CONCAT(first cell, second cell, third cell).”

The concatenate function does not add spaces between the texts. If you want a space to appear, simply use quotation marks and insert a space. After doing that, your function is like this “=concat(“first cell,” “,second cell,” “,third cell”).”

If you want to make use of this formula for a set of rows, just click the blue box present on the edge of the cell and then drag down.

Wrapping Text in Excel

Long text that extends over the adjacent columns is not a good sight. You could make the column wider to contain the text, but this is not a feasible option when there are too many columns to display. A much better solution is to wrap the text so it appears on multiple lines, rather than a single long one.

To wrap text, select the cell with the long text in it

Right-click and choose Format cells

Check the box alongside Wrap text, under the Alignment tab

Adjust the cell width to suit your requirements

How to Activate the Developer Tab

Advanced tasks in Excel, such as using the Macro tools or ActiveX controls require access to the developer tab, which is not visible.

To display the Developer tab, choose Excel in the top-left corner of the spreadsheet

Then select Preferences

Click the Ribbon button

Check the Developer box

The developer ribbon will be displayed on the top

Now that you know some Excel tips and tricks, it's time to get out there and show your spreadsheet skills. These tips will make you more efficient at work, and will save a lot of time. Excel is a vast application, and it is not possible to learn all its features within a short span of time. The best way to master the program is to take one step at a time, and get familiar with it as you work.

Will these tips help you hold unique command over spreadsheets? Let us know your experience here.

Posted 20 September, 2017

LucyKarinsky

Software Developer

Lucy is the Development & Programming Correspondent for Freelancer.com. She is currently based in Sydney.

Next Article

IQ vs Passion