7 Excel Skills you MUST have!

Hi,

The fact that you and your company use Excel for managing business data means that everyone will benefit if you increase your Excel skills: you, the end users who receive your reports, even your boss will love you.

Improved Excel skills will have a significant impact in your overall productivity and marketability.

This is so true because…

  • You analyze data in Excel
  • You communicate information using Excel
  • Your end users use Excel

The good news is that improving a few skills will have a huge impact in your work. Here you will become aware of 7 “must” Excel skills.

So if more rapid career advancement sounds good, let’s start…

1) Organizing your Workbook

Here are some reasons to keep your workbook and sheets organized…

Become more efficient because you won’t be disoriented in your own sheets, get more insights because you look at the data that matters (no cluttered sheets), you and your end users will  never guess about where the inputs/outputs are, etc.

Also, organization creates patterns; these patterns will allow you to be aware of weird things so you avoid/detect errors early.

At the end, you will be perceived as a skillful professional, earn more money, be promoted and get more good things happening in your life.

Here are some tips about how to organize the data in your Excel workbook…

The best piece of advice I can give to you is this: use formatting and sheets purposefully.

What does this mean?

For instance: use red color to show errors and not because you like the red color. People are comfortable when colors remind them of similar things. For example: red = danger, orange = warning, green = good things, etc.

Create sheets for the purpose of collecting data, presenting data, writing notes, etc. This way you won’t clutter your spreadsheets with intermediate steps that increase the size of your books and not the value of them.

Organize stuff at the Workbook level…

  • Create a Cover Sheet where you have the title, purpose, etc
  • Define a Contents Sheet where you have the constraints of the sheet, some directions, etc
  • Have a sheet for the Schematic of the model
  • Have a sheet where you present the data, enter the inputs, make the calculations, etc
  • Have a Blank Sheet where you store comments, future reviews, make test calculations, etc
  • Have a Lookup Sheet where all the lookup formulas take the data from
  • Define a Chart Sheet when appropriate
  • Keep formatting consistent across all your books: sheet title position, column and row dimensions; hyperlinks, visibility of gridlines, grouping levels, zoom level, window panes and splits, and formats and colors

Define three things at the Excel Cell level

  • Clearly identify the input cells. Use a consistent format across your books
  • Define a separate format for output cells. This procedure will prevent you and your end users from ruining your spreadsheets by replacing formulas with inputs
  • Use a special format for mixed cells (input and output). These type of cells are those which have a constant inside the formula

The following skill is worth having too…

2) Manipulating Cell Content (mainly strings)

You know that data usually comes from different sources you cannot control: external software exporting process, manual compilation, .pdf to Excel conversions, csv files, etc. The fact is that you will always deal with data that doesn’t comply with your formats or the standards of Excel.

Here are some tactics to manipulate strings in cells…

  • Get rid of leading, trailing and inter spaces with the TRIM function
  • Get rid of leading zeros. For example: remove them from the string “0000123123456789012” with a formula like this one: =RIGHT(A1,LEN(A1)-4) so you get “123123456789012”
  • Extract the portions you need from any string. For example, extract the left side of the string “eee-444” with the formula =MID(A4,1,FIND(“-“,A4)-1) so you get the text “eee”
  • Perform the majority of string manipulation tasks using the Text functions: LEN, FIND, LEFT, MID, RIGHT
  • Use the Text to columns command when you have text with a regular spacing or a clearly defined delimiter (, or ; or -)

Here is the third essential skill…

3) Formatting Cell Data

Your end users require to see data in as many formats as users exists. Being able to customize how your cells look is critical to deliver your data to every recipient.

Additionally, formatting your cells make your sheets and models more readable. That’s not all, if you combine the data with conditional formatting you can gain powerful insights.

Here’s how…

Formatting cells

The shortcut CTRL + 1 triggers the Format Cells dialog. The use of the dialog is very intuitive. Just pick a format you want and the cell content will be shown accordingly. Keep in mind that the format doesn’t affect the underlying content.

Here are some examples…

  • Numbers can be shown in different ways: with the desired number of decimal places (100.12, 100.123), with currency symbols ($100.12) with a suffix or a prefix (100.12 m3 by using the custom format #,##0.00 “m3”)
  • Texts can be shown as plain text or with a prefix, for example *Joseph by using the custom format “*”@ or suffix @ “*”
  • Dates can be shown as: Friday, March 19, 2010; 19-Mar, etc

 

Conditional formatting

You can color your cells based on an established rule. For example if the cell value is less than 0 then format it red and bold.

Access the command in Excel 2003 this way Format>Conditional Formatting or in Excel 2007 this way: Home>Styles>Conditional Formatting

Some of the above formatting can be done massively with a mouse click; you can do it easily if you learn the skill of…

4) Recording a Basic Macro

Many of the tasks you do every day before you start working on what really matters is routine: inserting a column, naming a column, applying borders to cells, changing the font size, etc.

If you package all this routine work in a macro, you will save tons of time. Additionally, you will cover more ground because you don’t get drained of energy due to excessive manual work.

Here’s How…

This is a very simplistic explanation but it shows the handiness of the Macro feature.

  1. Record a macro (Excel 2003: Tools>Macro>Record new Macro or this way for Excel 2007: View>Macros>Macros>Record Macro)
  2. Perform the operations: hide columns, apply borders, write field names, etc
  3. Stop the recording
  4. Run the macro (ALT + F8) each time you want to repeat the above operations

The power is unleashed when you modify Macros and write your own code.

Things start to become more interesting!

Having the next skill will give you a competitive advantage…

5) Crunching Data

Being skillful at crunching data in Excel is critical because…

The more you manipulate data with ease, the more you can find patterns, the more patterns you find the more insights you get, the more insights you get the more knowledge you gain and can present to the team, so everyone makes better decisions for the business.

You can also increase your focus on specific figures or categories in big tables with thousands of rows.

The two commands that primarily allow you to crunch tables are: Filter and Pivot table.

Filter allows you to filter by category or by criteria. For example: you can filter all the sales made by “Susan” that are greater than 10,000 and then filter out the products that are not “Electronics”.

You can copy and paste the filtered data so you split tables very easily.

On the other hand, a Pivot table allows you to create reports in seconds by compacting categories and showing you the related attributes. For example: sales by category, sales by category and by product, average sales amount for each product, identifying the three best-selling products within each group, All Quarters Sales for each product and group by quarter, etc

You can increase the power of a Pivot table results by applying filter criteria to the fields.

Here’s a skill I really love…

6) Combining Functions

Successful Excel users don’t retype data, they call other cell content by using cell references, functions and functions inside those functions.

As a result, a sheet with functions that call other functions/references is not static. What does this mean? Any change in your input cells update the entire model.

Here’s How…

The first thing you need to do is to learn the Excel function, after that you need to learn two key things: 1) what the function retrieves (value, cell reference, array reference, TRUE, FALSE, etc) and what each argument is (value, reference, etc).

You can use a function to feed each argument given the fact that the function retrieves the same type of data as the argument.

Here’s an example

IF(logical_test,value_if_true,value_if_false)

The argument logical_test needs a TRUE or FALSE value or any other function that retrieve such values, examples:

  • IF(AND(A2=0,B2=”Susan”),value_if_true,value_if_false)
  • IF(A2>C2,value_if_true,value_if_false)
  • IF(A2>MAX(D2:D10),value_if_true,value_if_false)
  • IF(A2>1000,value_if_true,value_if_false)

And to really add versatility to your work you need to have the following skill…

7) Extracting Information from Tables

What happens when you start creating reports exactly as “End-users” want?

Your boss is most likely to say, I like the report you sent me…it allowed me to make a decision right away. Could you prepare this other one for a client?, etc…because you have provided him new insights about a key business metric, he is now confident in trusting you, knowing his life will be easier.

The fact that data comes in tabular form and the fact that we need to get access to specific figures of those tables, implies that the increase in your skills to manage tables, will give you a cutting edge advantage.

Here are some formulas you can use to extract information from tables

  • VLOOKUP It allows you to search a value in a back end index column and to retrieve the corresponding value from another column. It only searches values on the left and retrieves values to the right. For example: search the Product Code and return the Product Price
  • INDEX & MATCH It has the same VLOOKUP functionality but it is not limited to search only the left column
  • Array Formulas allow you to search multiple columns and complex computations. For example: search the Orders made by Susan in August and return the total
  • SUMPRODUCT It is a kind of Array Formula but more readable
  • SUMIF It allows you to sum various lines that match one condition. For example: sum all the Sales made by George
  • SUMIFS It allows you to sum various lines that match multiple conditions, these conditions are evaluated in multiple columns. For example: sum all the Sales made by George in August
  • COUNTIF It allows you to count various lines that match one condition. For example: count all the Yes Answers
  • COUNTIFS It allows you to count various lines that match multiple conditions, these conditions are evaluated in multiple columns. For example: count all the Yes Answers of Female respondents
  • AVERAGEIF It allows you to average various lines that match one condition. For example: average all the Sales made by George
  • AVERAGEIFS It allows you to average various lines that match multiple conditions, these conditions are evaluated in multiple columns. For example: average all the Sales made by George in August

You may be aware that “knowing” about the Vlookup and Hlookup functions and “using”  the functions are on very different levels. Once you face the wall of several conditional choices, multiple column criteria and complex criteria, then you need to look for formulas like SUMPRODUCT, INDEX and MATCH, and Array formulas.

Just one more thing…

7 + 1) Compiling Excel Tips

If there is a “shortest route” to perform a task, then you need to know it. That shortest route is often called a tip.

An Excel tip allows you to have quick access to tools so you will save time, increase your productivity and have fun.

Saving time does not mean you won’t do those tasks, the key is doing them faster, with less keystrokes and with a packaged sequence of strokes.

You find tips on the internet and you will develop your own toolbox with time, as you nurture your tip hunting mindset.

That’s all for today!

All the best

John Franco

“Boost your Excel Skills and you’ll boost your career”