3 Tips: Turn Your Excel Skills From Good to Great!

Looking for some ways to improve your Excel skills to reduce errors, save time and/or take you to a new level of category management analysis?

Excel is a great tool and necessary program for almost anyone in business to know how to use. But there’s always more to learn, faster ways to do things, shortcut keys, and helpful formulas. It can also waste an incredible amount of your time and be very inefficient without utilizing Excel’s powerful features through a solid understanding. If this sounds familiar, you are not alone — there are a lot of people in this camp because many of us have learned on-the-job rather than through training. The opportunity? 

Learn more effective Excel skills, save time and frustration and transform yourself into an Excel rockstar! 

Here are some resources to help you build your Excel skills:

1. Course Video Preview: Building Excel Skills
2. Course Overview: Building Excel Skills

1. Effectively Move Around Your Workbook

INDICATOR: You primarily move around your workbook or spreadsheet using the up and down arrows and/or scrollbar. 

SYMPTOMS: May have blisters or callouses from too much clicking and maneuvering; may experience dizziness and confusion from watching your screen flying down and left and right as you try to get to your final destination in the worksheet; may correlate how fast the screen is flying before your eyes with efficiency (but it’s really not!).

REMEDIES: 

1. If you know the cell that you want to get to, press F5 and then the cell number (e.g. D3251);
2. Use the following keys / key combinations in the table below to move around your worksheet like a pro – you should print off this image and have it beside you – once you practice them a few times you’ll wonder how you ever lived without them!

2. Learn (and Understand) Formula Writing

INDICATOR: You avoid using formulas like LOOKUP, MATCH or INDEX because they are too difficult to understand OR you copy them from somewhere else and hope they work OR you get someone else to figure it out.

SYMPTOMS: When you look at a “scary” formula you get sweaty palms and your heart rate increases; you ask someone else to figure it out because you “suck” at Excel; you find a different way to calculate the numbers.

REMEDIES:

1. Understand “Syntax”

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

2. Example LOOKUP formula

If you really want to get better at Excel, understanding the structure of a formula will help you more easily and efficiently use formulae (it’s really not that complicated!). 

3. Learn to Master PivotTables!

If you don’t know what PivotTables are or how to create one, you need to learn this really important tool in Excel. PivotTables allow you to sum up, count or complete calculations from a large database across multiple levels. Here are two great tips for PivotTables:

1. Turn off “GetPivotData”

If you’ve ever linked a formula to a PivotTable, you may notice that the range reference changes to a “GetPivotData” link that can mess with your formula. To avoid having formula references that use GetPivotData functions for PivotTable references, go to Excel Options, Formulas, and turn off “Use GetPivotData functions for PivotTable reference”. I didn’t know this one until a student shared the tip through our student webinar on Excel.

2. Create a Dynamic Range Name

If you have a PivotTable that links to a database size that changes every time you update the data, you need to know how to create a dynamic named range. You can build a dynamic named range using offset formula.

• Create dynamic named range (in my example I named it “pivotdata”). While you’re on your database worksheet select <Formulas><Define Name> =OFFSET('Database'!$A$1,0,0,COUNTA('Database'!$A:$A),19).
  • For the 'Database'!$a$1 reference, this is the top left corner of where your database starts; the 'Database'!$A:$A) is the first column reference — my database starts in column A.
  • The "19" at the end of the formula refers to the number of columns in your database.
• Then create your PivotTable while referring to the dynamic named range called ‘pivotdata’. From now on, your PivotTable will adjust to the changing size of your database – amazing!

In short, never assume you’re finished learning Excel — adding a new skill can save you time, reduce errors and/or improve your category management data analysis skills. There’s always more to learn!

Looking for even more information to build your Excel and worksheet skills? Category Management Knowledge Group can help you, your team or your organization learn more through a single course or a customized program. We have some great category management training options available to meet your needs.

Check out our accredited course on “Building Excel Skills” or watch a video preview of the course by clicking the graphic below.