Tools Archives - Excel Campus

Archive

Category Archives for "Tools"
25

3 Ways to Remove Duplicates to Create a List of Unique Values in Excel

Bottom line: In this article and video I explain 3 ways to remove duplicates to create a list of unique values. We look at the Remove Duplicates feature and a macro that makes the process much faster.  I also added a video on how to use the Advanced Filter to list unique values.

Skill level: Intermediate

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

Continue reading

11

How to Install an Excel Add-in – Guide

What’s an Excel Add-in File?

An add-in file is an Excel file that contains macros (code) and has the extension “.xla” or “.xlam” (2007 and later). When opened, the worksheets in the file are hidden and the macros are typically accessed through buttons that are added to the toolbar or ribbon. Once installed, an add-in will automatically open every time Excel is opened.

Installation

Here is a video that walks through how to install the add-in.

Please see my video below on how to add the add-in’s folder location to the Trusted Locations list.  This is now a critical step due to a July 2016 Office Security update.

Installing the add-in is pretty easy, and should only take a few minutes.

  1. Save the add-in file to your computer.
    – Pick any folder on your hard drive that is easy for you to find.
    – My suggestion is to create a folder named “Excel Campus” and place it in your Documents folder.
  2. Open Excel and go to the Options Menu.
    – This varies depending on your version of excel.
    – 2007 – Office Button > Excel Options
    – 2010 & 2013 – File > OptionsExcel Options Menus 2007 2010 2013 2016
  3. Click the “Add-Ins” option on the left-side menu, and then click the “Go…” button.
    – You may have to wait a few seconds for the window to appear.Excel Options Add-Ins Menu
  4. Click the “Browse…” button on the Add-Ins Menu.Browse for Excel Add-ins
  5. Locate the add-in file you saved in Step 1, and click OK.Select Excel Add-in File
  6. The Add-in will appear in the list and should be enabled, click OK.Enable Excel Add-in
  7. Installation Complete (almost) – The add-in is now installed.  However, there is now an additional step due to an Office Security Update in July 2016. Please see the section below for instructions.

Trust the File Location

Important Note: There is one additional step to this installation due to an Office Security Update released in July 2016.  Here is a video that explains it in more detail.

The folder that the add-in file is saved in needs to be added as a Trusted Location in Excel.  The instructions on how to trust the folder location are below.  I also have an article and video that describes this issue in more detail.

  1. Open the Excel Options menu
    1. File > Options
      Excel Options Menus 2007 2010 2013 2016
  2. Open the Trust Center menu and Add a new location
    1. 1. Trust Center > 2. Trust Center Settings… > 3. Trusted Locations > 4. Add new location.
      Add New Trusted Location Folder in Excel
  3. Browse for the folder that you saved the add-in file in.
    Add Trusted Location Folder
  4. Press OK, the folder should now appear in the Trusted Locations list.
    Trusted Location Added for Documents Folder
  5. Press OK on the Trust Center and Excel Options menu to close the menus.
  6. The add-in is now in a trusted location and the XL Campus tab will appear every time you open Excel.

Unblock the File

Some users still have issues with the add-in’s ribbon disappearing after trusting the folder location.  If this happens to you, you will need to Unblock the file by changing a file property.

  1. Locate the Add-in file (.xla, .xlam) in Windows Explorer.
  2. Right-click the file and select Properties.
  3. At the bottom of the General tab you should see a Security section.  Check the box that says Unblock.
    Security Unblock Property for Excel Add-in - Ribbon Disappears
  4. Press the OK button.
  5. Close Excel completely and re-open it.  The add-in should now load and any custom ribbons will appear.

You will only need to do this unblock one time.  However, if you download an updated version of the file then you will have to repeat the steps above to unblock it.

Hopefully these additional security steps will be fixed in a future update to Office.  If you completed all the steps above then you should see the add-ins ribbon tab load every time you open Excel.

Download PDF

Installing An Excel Add-in - Guide (672.2 KB)

Add-ins

Paste Buddy – Create custom keyboard shortcuts for the paste special commands like paste values.

Formatting Shortcuts Add-in – Keyboard shortcuts to change font & fill color and cell styles.

More Add-ins

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