Hi folks!

Before beginning the article, I just wanted to thank Brian Allan for starting an interesting discussion on what “Strong at Excel” means; without his topic, I wouldn’t have written this.

Also, thank you to Bernard from the ACP AdvisorNet team for aiding me through the process of writing this article.

One house-keeping tip before we dive into the (wonderful) world of Excel: Excel is a highly visual program. As I’m not able to insert images in the article, at times it might be hard for you to visualize what’s going on.

So, I’ve created this guide – with images – which is available for free download here.

I’ve also created a free sample file, that makes it a lot easier to practice, and make sense, of the examples I’m about to go through. Download that here.

One last thing, if this is your first time opening Excel, some of these tips might be too hard to get right away (instead, start with this guide on how to use Excel – then return back to these 5 tips).

Okay, with this out of the way, let’s get into the details!

Have you found yourself browsing for job openings only to find listing after listing requiring some level of Microsoft Excel skill? Or maybe you are currently in a role where you feel you could be getting more accomplished if you could just add a few of the right skills quickly.

In this article, we will cover 5 top Excel skills that will elevate your expertise immediately!

While these tips barely scratch the surface, each one can have an immediate impact and get you noticed. These are all very simple to learn and each will set you on a path to further skill development.

Let’s get started!

Tip #1: Text-To-Columns

Often, you will be presented with raw data that needs cleaning up. It will need some Excel magic to make it usable.

Let’s consider a pretty common case where you have address data in a single column, but you need to quickly parse out the city, state and postal code into their own separate columns.

The first thing to do is select the proper column by clicking its letter above (for instance ‘A’). Next, find the ‘Data Tools’ group on the Data tab and click on ‘Text to Columns’.

The ‘Convert Text to Columns Wizard’ will now appear. Step 1 of 3 detects that our data has a delimiter – commas (change that if you need to). Click ‘Next’.

In Step 2 of the wizard, you will now uncheck the ‘Tab’ box and instead check the ‘Comma’ box to make sure the text gets separated at the commas.

Notice that the ‘Data preview’ textbox shows you the data how it looks now that it’s been separated where the commas were. Click ‘Next’ to move onto step 3.

This step allows you to change the data format and choose the destination for the data. By default, the destination is the top of the original range. When we click ‘Finish’, the data in our sheet now resembles the data preview from the wizard. We can now change and add headers to fit our needs.

Tip #2: Conditional Formatting

Conditional formatting allows for the use of color fill and icons to bring visual meaning to your spreadsheet data. There are many default formats available but you can also create your own rules using formulas.

One of the more useful default conditional formatting settings is one that highlights duplicate values in a range. First, make sure the relevant range of data is selected, and go to the ‘Styles’ group on the Home tab and click on ‘Conditional Formatting’.

Hover over ‘Highlight Cells’ and find ‘Duplicate Values…’ in the menu list that appears.

Now the ‘Duplicate Values’ dialog appears. Simply click ‘OK’ to accept the default.

Note the dropdowns that contain default rules available and some default formatting options. Now all of the duplicate values in our range will be highlighted with red text and pink fill.

Tip #3: Pivot Tables

Another must-have skill you need to look like an Excel pro is the ability to create a Pivot Table. While this skill seems impressive, it’s also not really that difficult to learn.

Let’s say you have some sales data. It’s simply sales totals by region and quarter. Sure, we could simply do some custom sorting, but creating a Pivot Table is just as simple and will allow more flexibility.

Simply click a cell within the data range and then click on Pivot Table in the ‘Tables’ group of the Insert tab.

The Pivot Table wizard will appear.

Select ‘Existing Worksheet’ for the placement of the Pivot Table report and click ‘OK’. Now you will see your Pivot Table area anchored in the cell you previously clicked.

If your version of Excel has ‘Classic’ selected for Pivot Table layout, don’t worry. This is the same thing.

Notice the PivotTable Fields window on the right side of the worksheet. Click and drag ‘Region’ from the available fields area to the Rows area below it. Then drag ‘Quarter’ to Columns. Last, drag ‘Sales Total’ to Values.

If ‘Sales Total’ appears as anything other than ‘Sum of…’ in the Values area, just right click on one of the values in the Pivot Table and find ‘Value Field Settings…’. There you can select from different options like ‘Count’, ‘Max’, and ‘Average’.

That’s it. You now have a nice summary of sales by region and quarter. Now you can experiment with changing columns and rows for a different visualization of the data.

Tip #4: VLOOKUP

Another one of those notoriously pro skills is knowing how to use the VLOOKUP. This built in function has four arguments.
• ‘lookup_value’
• ‘table_array’
• ‘col_index_number’
• ‘range_lookup’

Let’s say we have a product table that list color and price by catalog number.

Now we create a lookup table where we can insert a catalog number value and get the color and price on the go.

We will do this by inserting a VLOOKUP formula into the ‘Color’ and ‘Price’ lookup fields. Let’s start with ‘Color’. Since this is a formula, we start with an “=”. Then type ‘VLOOKUP’ and tab. This will add an opening parenthesis.

The tooltip appears and shows the necessary arguments. Here, we will select cell F2 as our ‘lookup_value’. This is where we will enter the catalog number that we want to lookup information for. Our ‘table_array’ argument will be the entire range of our product table, and the ‘col_index_num’ will be the value ‘2’. This stands for the second column of the ‘table_array’. Finally, we will insert FALSE for the ‘range_lookup’ argument. This simply tells Excel we want an exact match.

Note that the ‘#N/A’ error currently appears. This is because we have yet to insert a value in F2. When we do insert a catalog number, our VLOOKUP now shows us the color listed for that product.

Now we can repeat the process for ‘Price’ by simply changing the ‘col_index_num’ argument to ‘3’.

Tip #5: Charts

One last skill to discuss that will put you at pro status is being able to insert a basic chart.
Charts are all about data visualization and they add value to any data set.
For the sake of simplicity, we will use the sales by region and quarter from our Pivot Table example.

To create a chart with this data, simply click on one of the cells in the range, find the ‘Charts’ group on the Insert tab and click on either ‘Recommended Charts’ or the little arrow in the bottom right hand corner of the ‘Charts’ group. This will expand the ‘Insert Chart’ dialog.

Excel automatically offers its recommendations for the data set. We could go with the first clustered columns chart but let’s scroll down to the bottom and select the stacked bar version with quarter shown as the vertical axis and sales amount as the horizontal axis.

This will give us a valuable visualization of the data where we can not only see the total sales per quarter in aggregate, but the stacked bars allow us to see the breakdown of that total by region. Click ‘OK’ and now you have your chart. Note that you can drag and drop the chart wherever you like. Also, the Design tab is now activated in the ribbon showing you the different options for tweaking your chart.

One final thing we can do is click on the Chart Title and type our own custom title.

There you go!

Five quick and easy skills to learn in Excel that can make you look like a pro. Clearly, it’s not too difficult to get acquainted with the basics of these highly sought after Excel skills. Now you can take the next step by exploring more about each of these.

If you have any questions or comments, please let me know!