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

Posted by Sue Nicholls, CPSA Founder & President CMKG | Aug 25, 2015 7:20:00 AM

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

Looking for 3 tips that can help you (no matter your current Excel level) to become more efficient and reduce the chance for errors in your Excel workbooks? Read on and enjoy —

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!

Excel_skills_in_category_management


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”

When you start using more sophisticated formulas, it is important that you understand the syntax associated with the formula. The syntax refers to the layout and order of arguments in the functions or formulas. If these arguments are put in a different order than the syntax dictates, the function will return an error message or give you an answer that you were not expecting.
 
As an example, here’s the syntax for the VLOOKUP formula. It may look difficult to understand right now, but if you start to understand the layout of the syntax for different functions, you will be able to start using it as a guide when creating more difficult formulas. This “syntax” shows up whenever you start to type a formula into Excel and prompts you what the formula is looking at next.
 
Here’s an example of a syntax for the VLOOKUP formula:

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

  1. Example LOOKUP formula

For the LOOKUP formula, we need to identify the value that we are looking for, the range of values to find it in, and then once it’s found in the range, what corresponding column of information you want returned. The range lookup type allows you indicate if you are looking for an exact match to your lookup, or a closest match.
 
It’s hard to understand a formula without going into Excel and seeing it in action. Here’s a demo video on LOOKUP formulae which will help you understand syntax better and also teach you how to understand a very powerful 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.

Category_Management_PivotTable

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!

blog_pivot_table_2

 

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


Watch a video guide of "Excel Demo Lookup & Reference Formulae" from Category Management Knowledge Group

 

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. 

 

 

   

Link to Purchase “Building Excel Skills” Category Management Training Course

$125 USD
INCLUDES:

30-day Access

Hands-On
Workshops

Downloadable
Notes

Reference Guide

Knowledge Checks

Course Test

 

Topics: Category Management Training, Category Management, Analytic Skills

Written by Sue Nicholls, CPSA Founder & President CMKG

Category Management is my passion.

Since beginning with P&G in the late 1980s, I have dedicated my work life to building and sharing this passion with others through active involvement in the industry, including long-term business relationships with large Retailer and Vendor executives, development and influence on Category Management Association certification standards and curriculum, thought leadership publishing and presentations at CSP News’ Convenience University, CMA’s Annual Conference and LinkedIn, and as a member of DePaul University’s Center for Sales Leadership Advisory Board.

Through this blog and other channels, I share this expertise and believe that an open and ongoing conversation can improve any team’s capacity to implement business strategies that achieve their strategic priorities.

help desk software