Jon Acampora - 15/16 - Excel Campus
Jon Acampora

Author Archives: Jon Acampora

8

Comparative Distribution Chart – Histogram or Box Plot Alternative

It can be difficult to create visualizations that compare one segment against an entire population of data while displaying the distribution of the entire population.  In this post I’m going to explain how to create the following chart in Excel.  

Comparative Distribution XY Scatter Chart

In this case we want to see pricing distribution for several products by customer segment.  So the data values are average price, and the categories are the products and customer segments.  But this same technique could be used for any combination of data value and categories; sales by product and region, headcount by department and country, etc.

The Challenge

I was recently doing analysis on product pricing data and the goal was to determine how one customer segment was performing against all the rest.  How does the the average price of each product in Segment 1 compare to the rest?  Like all good charting or data visualization projects, it took many iterations to come up with a chart that clearly communicated the story without too much explanation.  

Alternative 1: Box Plot or Quartile Plot

I first started with the box plot or quartile plot.  This is a great way to see the distribution of your data and compare it to other segments or categories.  The major issue I had with the box plot is that not everyone understands it.  So the use of a box plot depends on your audience.  If the audience is familiar then it is a great solution.  However, trying to explain it can be time consuming and not worth the effort.

Box Plot or Quartile Plot Product Comparison

Box plots also work well if you have a large number of segments/categories.  In the comparative distribution chart we are only looking at 5 different customer segments.  If you had hundreds or thousands of segments, then the box plot is probably a better solution.  I will explain how I created it in a separate post.  If you want a hint, it’s actually a line chart turned on its side.

Alternative 2: Histograms

Histograms are a good alternative for a single category, but comparing multiple categories doesn’t really work.  You could combine several histograms into a panel chart, but it is hard to identify trends between categories.

Histogram Panel Chart

How to Create the Comparative Distribution Chart

 

There are two files you can download below that will help guide you through creating this type of chart.  

  • The “Comparative Distribution Chart Guide.xls” file contains a detailed step-by-step guide.  
  • The “Comparative Distribution XY Chart.crtx” file is a Chart Template file that you can use to change the chart type to resemble the comparative distribution chart.  This will save you a lot of time in formatting the chart.

Step 1 

Your original data should look similar to the format below, with products in each row and columns for each segment.  Using a pivot table to summarize your raw data would be an easy way to get the data in this format.  

Comparative Distribution - Original Data

Once you have the data table, then you need to add a few columns that will be used to plot the points in the XY Scatter chart.  I’ve added cell notes in the guide file that give more detail on the calculations in each column. Comparative Distribution - Range Bar Table

Step 2

Create the XY Scatter chart and add all the data series.  It’s best to select a blank cell and then insert the “Scatter with Only Markers” chart type.  Then add each data series individually.  Excel has a tough time trying to automatically figure out the X and Y values for each series if you try to select the whole table and create the chart.  So it’s best to add each series one-by-one.

Comparative Distribution Chart - Step 2

Note: You can skip steps 3 and 4 below by applying the Comparative Distribution XY Chart template.  This will automatically do all the formatting for you.

Step 3

Now that you have all the series plotted on the chart, you need to format the marker options and line colors/styles for each series.  You’ll want each series to have the same marker style and color except for the series you are comparing.  In this case we want Segment 1 to have blue circle markers, and all other segments to be gray.  The Range Bar series is the light gray background bar that shows the range from min to max for each product.  For this series, set the markers to None, and change the line style width to 8.5pt.  This will create a thick line in the background.  You may also have to rearrange the order of your series if the background bar is on top of the other points.

Comparative Distribution Chart - Step 3

Step 4

The chart axes need to be changed so the data points are plotted between the horizontal grid lines.  The vertical axis needs to be changed by starting the minimum axis at 0.5 and changing the major unit to 1.0 on the vertical axis.

You can also change the major units on the horizontal axis to reduce the clutter.  We really only need to see the min and max values and maybe a few points in between to give some scale to the chart.

Comparative Distribution Chart - Step 4

Step 5

Add labels for the product and Segment 1 price.  The fastest and easiest way to do this is by using the XY Chart Labels add-in.  It’s available for free download and very easy to use.

http://www.appspro.com/Utilities/ChartLabeler.htm

XY Chart Labeler - Add Labels

Step 6

Finally, put some finishing touches on your chart to make it look presentable.  Tuck in its shirt and comb its hair. 🙂  And basically remove all the unnecessary chart junk that is not needed to tell the story.  We are trying to clearly show how Segment 1 compares to the other segments across all product lines.  

Comparative Distribution Chart - Step 6

Pros and Cons

Pros

The comparative distribution chart combines a little bit of both the box plot and simple histogram.  With the added bonuses of being easy to explain, and allowing for comparison of one data point against the whole data set.  It’s use will depend what trends or messages the chart clearly conveys to the reader.  In this case the Segment 1 prices are lower than the others for almost every product.  That would be a clear indication that Segment 1 has some defining characteristics that create this behavior.  Possibly, Segment 1 customers always use coupons that other segments don’t have access to.  

Cons

This chart is best for small number of segments.  If we had 50 customer segments instead of 5, then it would be difficult to see the distribution of all the data points in the range for each product.  A box plot would be better suited for this.

Download

Comparative Distribution Chart Guide.xls (233.0 KB)

Comparative Distribution XY Chart Template.crtx (5.5 KB)

This model could be further enhanced by adding a drop-down to select the segment you want to compare to the others.  I’m sure you will find many possibilities for modifying it.

Please let me know if you have any questions.  I’d like to hear how you could use this or improve on it.  Thanks!

79

Excel Formula to Calculate Commissions with Tiered Rate Structure

In this post I will explain how to calculate a dollar or percentage commission payout in one cell using the SUMPRODUCT function.  Calculating commissions on a tiered rate structure can be difficult because you are trying to determine the cumulative payout based on different rates at each tier, and the achievement amount might fall in between one of the tier ranges.

If your commission plan tiers are not cumulative, then you might want to checkout my article on how to calculate commissions with VLOOKUP.  This is a simpler calculation then the one presented below.

Tiered Rate Table

The following is an example of a tiered rate table for sales commissions.  The first column contains the tiered ranges of Quota Attainment and the second column contains the Payout % for each tier.  If the sales person (rep) achieves sales that are at the top end of each tier, then they will receive the full payout amount in the Total Payout column.  For example, if the rep sells 40% of their quota then they will receive 20% of their commission.  If they sell 60% of quota, they will receive 35%, and so on down the table.

Tiered Rate Structure Table - Basic

The difficult part is when the attainment amount falls in between the ranges.  What if the rep sells 50% of his/her quota?  The rep would receive 20% payout for the first 40% of quota, and an additional 7.5% payout for the last 10% of quota.  The last 10% of quota attainment is calculated by finding the payout rate at each tier.  So the total payout on 50% of quota would be 27.5%.

Typically we would have to calculate the payout at every tier and then sum the payout amounts to get the total amount.  Or we could use some complicated IF statement to determine the payout all in one formula.  But there is an easier way…

The Solution

You can download the sample workbook below to follow along.

The SUMPRODUCT function can be used to calculate the entire payout.  First we have to calculate the differential payout rate for each tier.  The differential rate is the difference between the payout rates at each tier.

The payout rate at each tier is the total percentage of payout in the tier, divided by the total percent of attainment possible in the tier.  This is basically the amount paid for each percentage increase in attainment in each tier.  In the image below the Payout Rate for the 0%-40% range is 0.50.  This means that for every 1% attained, the payout will be 0.50 of the 20% total payout.

Payout Rate =([tier attainment max] – [tier attainment min]) / ([payout % this tier] – [payout % previous tier])

Tiered Rate Structure Table - Payout Rate Formula

Rate Curve

The payout rate is also known as the rate curve.  The rate curve is displayed below, and you can think of the payout rate as the slope of the curve at each tier.

Tiered Rate Structure Table - Rate Curve

Differential Rate

The differential rate is calculated by taking the difference between the payout rate in the current tier and the payout rate in the previous tier.

Differential Rate = [payout rate Current tier] – [payout rate Previous tier]

Tiered Rate Structure Table - Differential Rate Formula

This is used in the cumulative calculation of the payout percentage.  As the attainment moves up into multiple tiers, the amount of attainment left in each tier is multiplied by the differential rate.  The sum of all these is the total payout.

SUMPRODUCT Explained

The SUMPRODUCT formula for Total Payout is:

=SUMPRODUCT( (Attainment > [Tier Min]) * (Attainment –  [Tier Min]) * [Differential Rate] )

Variables in brackets [] refer to entire column in rate table.

Tiered Rate Structure Table - SUMPRODUCT Formula

The following splits the SUMPRODUCT formula into multiple columns and rows for a clearer visual of how the formula is calculating the total payout.

  1. Attain > Tier Min: Returns a “1” if the attainment is greater than the attainment tier minimum.  If attainment is 90%, then the condition is true for the first 4 rows and a value of 1 is returned.
  2. Attain – Tier Min: Finds the difference between the total attainment and attainment tier minimum. This is necessary because we are multiplying it by the differential rate.  So in the first row we are taking the entire attainment of 90% and multiplying it by the diff rate of 50%.  Each subsequent row is taking the leftover attainment for its tier range, and multiplying it by the payout rate that is leftover for its tier (the diff rate).
  3. Diff Rate: The differential rate for each tier.
  4. Product: Column 1 * Column 2 * Column 3
  5. Sumproduct: Sum of the Product column.  The total payout on 90% attainment is 87.5%

Tiered Rate Structure Table - SUMPRODUCT Explained

SUMPRODUCT Visualization

The following is a visual example of the Product column plotted on the rate curve.  Sometimes it is easier to understand when you see it visually.  The SUMPRODUCT formula finds the total payout in each tier based on the remaining balance of attainment multiplied by the payout rate in that tier.  This is basically a continuation of the rate curve at each tier to the total attainment of 90%.  In the chart below you can see that the dark grey lines follow the rate curve at each tier and then continue on the same curve to the 90% attainment (green) line.

Tiered Rate Structure Table - Product Point Rate Curve

  1. For the first tier it is (90%-0%) * (50%-0%) = 45%.  50% is the payout rate for tier 1, and 90% is the total attainment.
  2. The second tier is (90%-40%) * (75%-50%)  = 12.5%.  The 90%-40% is the remaining balance of attainment for tier 2, which is 50%.  45% (tier 1) + 12.5% (tier 2) = 57.5%
  3. Tier 3 is (90%-60%) * (200%-75%) = 37.5%.  The sum of the first three tiers is 95% (45%+12.5%+37.5%), which is higher than the actual payout of 87.5%.
  4. Tier 4 is (90%-80%) * (125%-200%) = -7.5%.  The negative product is a result of the payout rate being less in tier 4 than tier 3.  Or, the slope of the rate curve is flatter in tier 4 than tier 3.  That brings the total payout back down to 87.5%.

Negative Differential

This negative differential rate in tier 4 is important to note.  Not only does it make for a confusing calculation, it also tells you that the rate of compensation is not as great in tier 4.  For each additional percentage point of attainment, the sales rep is compensated at a lower rate than tier 3.  This might mean that there is more emphasis for the rep to attain sales on their quota in tier 3.  And the monetary motivation is not as great for attainment in tier 4.

Negative differential can also mean that the rate curve is poorly designed.  If the goal is to achieve 100% attainment of quota, then it is probably best to increase the payout rate (rate curve slope) in each tier up to 100%.  This design would give the rep more motivation (higher payout rate) as he/she gets closer to achieving their goal of 100% attainment.

Apply Formula to a List

Now that we are able to calculate the payout in one cell using one SUMPRODUCT formula, we can apply the formula to a whole list of employees in a table.  See the Total Payout % column  on Sales Table tab of the example file.  This is the major benefit of this formula.  The entire calculation can be handled in one cell and it is easily transferable to other models.  There are no hard-coded variables in your formula, or ugly IF statements.

Download

Tiered Commission Rates using SUMPRODUCT.xls (101.4 KB)

Here is a file that uses whole number (units or dollars) for the tiers and payouts, instead of percentages.

Commission Plan SUMPRODUCT Units Dollars.xlsx (10.6 KB)

Here is an alternate solution submitted by Matthew Burgos using VLOOKUP instead of SUMPRODUCT.  He explains the formula in detail in the comment below.

Tiered Commission Rates Using VLOOKUP.xlsx (12.1 KB)

Still Confused? 🙂

I was too the first time I learned this technique.  It’s definitely complex.  I’d recommend reviewing it a few times with sample workbook and then try to implement it in your own model.

I have another article on how to calculate commissions with VLOOKUP that is an easier calculation for a simple commission plan.

Please leave a comment below with any questions/comments about this technique.

134

Absolute Structured References in Excel Table Formulas

This post will explain a trick for creating absolute structured references in Excel Table formulas.

Structured Reference Tables are great for creating clean, easy to read formulas.  But creating absolute references to the columns (aka anchoring the columns) in the formula is a bit tricky.

Quick Guide

Duplicate the column references as if referring to multiple columns.  Absolute references to:

  • One column in the same or other table: table1[[column1]:[column1]]
  • One cell in the same row as the formula: table1[@[column1]:[column1]]
  • Table names must be used even if the reference and formula cell are in the same table.
  • You must drag these formulas across columns to maintain the absolute reference (copy & paste does not work).

I have developed an add-in allows you to use the F4 key on the keyboard to create absolute/relative references.  You can download the Absolute Reference Add-in here.

Video Tutorial

Continue reading

18

Convert Text to Numbers – Excel Keyboard Shortcuts

Excel has a built-in feature to convert text to numbers, but it can be tricky to use when you are trying to select a long list of cells or multiple rows and columns.  In the video below I describe how to use keyboard shortcuts to accomplish this very quickly.

Keyboard Shortcuts to Convert Text to Numbers

Here’s a quick guide for the keyboard shortcuts to select the range of cells you want to convert.

  1. Select any cell that contains the Number Stored as Text error.  You will see a small green triangle in the top left corner of the cell.Number Stored as Text Error
    _
  2. Use the following keyboard shortcuts to select the range:
    _
    Ctrl + A – selects the entire contiguous range
    Ctrl + A Twice – selects the entire sheet
    Ctrl + Space bar – selects the entire column
    Shift + Left/Right Arrow – selects multiple columns
    Shift + Space bar – selects the entire rowShift + Up/Down Arrow – selects multiple rows
    _
  3. The error menu drop-down will now be at the top left or right corner of your selected range.  You can then use the following keyboard shortcut to Convert to Numbers.
    _
    Alt + Menu Key + C – Convert to Number

Excel Convert Text to Number Keyboard Shortcut Alt+Menu Key+C_

The Menu Key is located between the Alt and Ctrl keys on the right side of the keyboard.  If your keyboard does not have a menu key, you can use Shift +F10 as an alternate.

 

Keyboard Diagram Menu Key Windows_

Alternative Methods

I find the built-in feature to be the fastest, but there are other ways to accomplish this task:

    • Copy a cell containing a 1 and Paste Special>Multiple on the range you want to convert.
    • Use Text to Columns tool on the Data tab (ribbon).  This is limited to one column and doesn’t work on rows.
    • Functions – the VALUE() function works well, but there are many ways to do this too.
    • VBA/Macro – write your own code to automate the process

Do you have a different or easier way?  Please leave a comment.

77

Zoom on Excel Charts

When you have a dashboard with small panel charts it is nice to be able to zoom in on the charts to see the trends better.  The ‘Zoom_Chart’ macro included in the workbook allows you to add a zoom button (shape) over the top left corner of the chart to zoom in on the chart.  It’s available for free download below.  The macro actually resizes the chart to enlarge it, then returns it to it’s original size when the zoom button is pressed again.  See the animated screen capture below.

Zoom on Excel Charts

Zoom on Excel Charts with VBA Macro

You can control the zoom amount by changing the percentage values for Zoom Width and Zoom Height in cells P5:P6 on the worksheet.  These can also be hardcoded in the code if you don’t want the user to change the size of the zoom.

Video Overview & Basic Tutorial

Implementation

The video above contains a detailed tutorial on how to add the zoom buttons to your own workbook.  It’s really a matter of copying and pasting the macro (VBA code) and buttons into your workbook, and then assigning the macro to the buttons.  You should still be able to implement this even if you aren’t familiar with VBA or macros.  It is important to line up the buttons on the chart correctly and give each button a unique name.  So it’s best to watch the video to make sure you don’t miss anything.  The steps covered in the video are listed below.

The zoom feature works really well with dashboards and reports where your screen area is limited.  If you would like to learn more about charting and dashboards I highly recommend the dashboard course from My Online Training Hub.  Checkout my full video review of the course and learn how to become an Excel Superhero! 🙂

Installation Guide

  1. Open the Zoom on Charts workbook and your workbook.
  2. Open the VBA Editor and drag & drop the code module into your workbook.
  3. Save your file as a macro enabled workbook.
  4. Copy the zoom button into your workbook.
  5. Place the zoom button on the top left corner of a chart.
  6. Assign the macro to the button.
  7. Change the zoom references in the code.
  8. Duplicate the zoom button.
  9. Give the button a unique name.

Enhancements

The VBA can be further enhanced to add data labels, legends, axis labels, and any additional chart components when the zoom in button is pressed.  Please leave a comment with some of the enhancements you made, or would like to see.

Download

 

Zoom on Excel Charts.xls (82.9 KB)

Zoom on Excel Charts - Bottom Right.xls (84.5 KB)

Zoom On Excel Charts - Top Right.xls (85.0 KB)

The “Bottom Right” file contains modified code that allows you to place the zoom button in the bottom-right corner of the chart.  The chart zooms from the bottom-right and expands up and to the left.  Zooming from the top-left or bottom-right are the only two options for button placement with this code.

The “Top Right” file contains code that allows you to place the zoom button in the top-right corner of the chart.  This option can cause problems if the chart is too close to column A and tries to expand beyond the left side of the worksheet.  The location of the chart will move and the chart will need to be manually resized and moved back to align with the zoom button.

Questions

Please leave a comment below with any questions or modifications you have made.

145

Find All VBA Form for Excel

The Find All Form for Excel allows you to type a search query in a text box and have the results appear in a list box.  The results are narrowed down as you type.  This is similar functionality to Google’s auto fill or auto suggest feature when doing a Google search.  See the animated screen capture below.

Find All VBA Form

Find All results in a worksheet or workbook

This is a very basic application that mimics Excel’s built-in Find All feature.  The code uses Chip Pearson’s FindAll Function in combination with the KeyUp Event in the text box to return results in the list box as you type. A sample workbook with all the code is available for free download below.

Possible Uses

The features and capabilities can be greatly expanded to meet your needs.  I’ve used this in an add-in that runs searches on general ledger (GL) account codes.  The user can quickly search for account codes from the GL or database, and quickly insert them into the worksheet.  In my application, the tables containing the account codes are stored in the add-in workbook.  I’ve also developed additional processes to automatically update the GL tables as they are updated in the system.  This makes the searches extremely fast since the underlying source data is stored in the add-in.

What could you use this tool for?  Leave a comment below.

Download

Find All VBA Form.xls (464.9 KB)

Find All VBA Form - All Worksheets.xls (1.2 MB)

File Find All VBA Form (Results on Form).xls (474.6 KB)

Find All VBA Form - All Worksheets - Copy Paste.xls (547.3 KB)

The “All Sheets” file above searches all the sheets in the file.  The sheet name is included in column 2 of the results along with the cell address.  When an item is clicked in the results, the resulting sheet and cell are selected.

The “Copy Paste” file above pastes the clicked item in a list on a Results Sheet.  This allows you to track which items the user clicks on in the results list on the form.

29

Excel’s Color Palette Compatibility Issue & Solution

In the following video I explain the difference between the color palettes and a solution for making your new workbooks look the same when opened in Excel 2003 or earlier.



Excel 2007 workbooks are Ugly in previous versions

The new theme based color palettes in Excel 2007, 2010, and 2013 are great for designing spreadsheets and charts that are visually appealing.  But did you know that those colors are being converted to the old 56 color palette when opened in a previous version of Excel?  This means your new spreadsheets could look like the following when opened by clients or colleagues using Excel 2003 or earlier versions.

Excel 2007 Table Colors Converted Earlier Version

Click to Zoom

The Previous Version Palette

Excel 2007 workbooks contain a 56 color palette that will be used when the file is opened by previous versions of Excel.  You can see this palette by opening an Excel workbook and going to: Office Button > Excel Options > Save (option in left sidebar) > Colors… button (after: Choose what colors will be seen in previous versions).

This is the default color palette in older versions of Excel, and Excel automatically converts your new theme colors to these colors.

Excel 2007 Color Palette Converted Earlier VersionFor users of previous versions to see the same colors you do, this previous version palette must be updated with the colors you’ve used in your workbook.

Does this sound confusing, time consuming, and frustrating? Well don’t worry…


Here is your simple and free solution!

The Color Palette Conversion Tool is a simple utility that retrieves the colors you’ve used in your workbook and updates the previous version palette automatically.  With the click of a few buttons you will ensure that all Excel users see the same colors you do in your fonts, borders, and background fills.  This is critical for presentation purposes, and general sanity if you have Excel 2007 at work and a previous version at home.

The tool has a few advanced features that allow you to control your previous version palette colors, plan for future designs, make updates in multiple workbooks, and easily view or restore default palette colors.  The tool is just a single worksheet in a workbook that contains macros to run this process.  You can use it as a stand alone, or add in to your workbooks if you want to make frequent updates.

Here is what your spreadsheets will look like in previous versions after using the Color Palette Conversion tool.

Excel 2007 Table Colors Converted with Tool

Click to Zoom

Save time and eliminate color confusion

If you’re currently going into your previous version palette and modifying colors, you know how time consuming it can be to change the colors.  You have to manually type in the RGB codes for each color you use from the new theme palette.  It’s a painful process, but must be done if you want your spreadsheet colors to be universal with all Excel versions.  This tool eliminates all that work.  I explain the color palettes in more detail on the download page.  I hope you find this tool useful and please post questions or suggestions below.

Color Palette Conversion Tool Screenshot

Excel 2007-2010 Color Pallette Conversion Tool Screenshot

Click Image to Zoom

Download Page & Instructions

Excel 2007 Color Palette Conversion Tool

Color Palette Conversion Tool

YFGY29S8735D

5

Format Multiple Excel Worksheets in Multiple Workbooks

Do you spend too much time formatting the same reports in Excel?

You probably find yourself formatting the same spreadsheets in the same way daily, weekly, or monthly.  This can be a very time consuming and redundant task.  And even if it only takes you five to ten minutes to apply page layouts or format header rows; those minutes add up and you may lose some consistency if you forget something.

The Format Copier is your Solution! (and it’s FREE!)

The Format Copier tool automates the process of applying page layout and formatting options to multiple sheets in multiple workbooks.  And you can store your formatted sheets, which we will call templates, to use when you have to format the same unformatted reports in the future.

How it works

The Format Copier is a workbook that contains:

  • The formatting tool
  • And your formatted worksheets (templates) that you will use to copy formatting to unformatted worksheets in other workbooks.

Format Copier Overview

With the press of a few buttons you will be able to apply your template formatting to multiple worksheets and workbooks.

Formatting Options include:

  • Page Layout Options for Printing
  • Page Breaks for Printing
  • Cell Formatting & Conditional Formatting
  • Outlines or Row & Column Groups
  • Freeze Panes
  • Hidden Rows & Columns
  • Column Widths
  • Row Heights

The Benefit: It’s Easy to Use and Saves You Time

You’ve probably already figured this out, but the Format Copier will save you lots of time.  Especially if you’re formatting the same reports over and over again.  The tool is very easy to use and even stores your formatting preferences so you don’t have to remember which formatting options you use for each template.

This tool will help if you spend time doing any of the following in Excel:

  • Insert header picture, image, or logo on all or select worksheets
  • Apply the same header logo formatting on all worksheets
  • Format multiple Excel worksheets
  • Apply page setup and print settings to multiple worksheets.

Never screw up your TPS Reports again!

Ok, that’s an Office Space reference… And with this tool you won’t ever have to worry about messing up your reports and angering your boss.  You might have a general ledger reporting software that exports reports in an plain looking format, or doesn’t format them at all.  The Format Copier will transform these reports to your specification, making them easy to read and print.  And you will produce reports that are formatted consistently, without having to dig up reports from previous months to compare.

Download Page & Instructions

Format Copier Logo

Format Copier

9

Excel Quick Access Toolbar – Keyboard Shortcuts

The Quick Access Toolbar (QAT) in Excel 2007 is extremely useful for creating keyboard shortcuts for items that don’t have a defined keyboard shortcut.  For example, there is no defined keyboard shortcut to Paste Values but you can easily set it up on the QAT.

The QAT is accessed through the keyboard by pressing the Alt key.  Press and release the Alt key and you will see numbers appear next to each icon on the QAT.

Alt Number Key for Keyboard Shortcuts for the Quick Access Toolbar

 

So Alt + [the icon number] is the keyboard shortcut for each item.  In this example, the keyboard shortcut for paste values is Alt+1.  The keyboard shortcut for the format painter is Alt+2.

There are two ways to use the keyboard shortcut.

  • Press and release Alt to view the shortcut numbers next to each icon.  Then press the number of the QAT item you want to use.
  • Better Option: Press and hold Alt then press the number of the QAT item.  This is much faster once you have your QAT shortcut numbers memorized.  You will notice that the numbers don’t appear when you press and hold Alt, so you have to memorize them.

I tend to move the icons around based on the task I’m working on in Excel.  If I’m going to be using the format painter a lot, I will move it to the “1” position (farthest left) while I’m using it.  This is mainly because it is easier to Press Alt+1, Alt+2, and Alt+3 on the keyboard by placing your right thumb on Alt and right index finger on the number.  The other numbers can be a bit of a stretch, depending on your keyboard.

See my post on how to setup the QAT for more details.