Create a progress bar with icons

Discover how you can create a progress bar with icons in your worksheet.

Step 1 : Import one icon

Go to the menu Insert > Icon

Select one of the icons and click on Insert

Select one of the icons in Excel

Step 2 : Crop the icon.

Select the icon and go to the menu Graphics Format > Crop

Menu crop image

Now, you can crop you icon.

Step 3 : Zoom on your icon

To crop your icon with the best precision, zoom to the maximum you can (400%)

Then reduce the border to adjust the size of the icon. Click on the button Crop to toggle off the crop mode.

Step 4 : Align to the grid

This step in not really compulsory but it's a way to align the icon to the grid

Select the icon and select the option Align > Snap to grid. Like that, your icon is perfectly located in a cell.

Menu Snap to grid

Important, once the icon is adjust to a cell, toggle off this option.

Step 5 : Duplicate the icon

To duplicate the icon, there is the pretty handsome shortcut Ctrl + D

Move the second icon close to the first one.

And here is the magic ! When you press Ctrl + D again, the position of the third icon is exactly after the second icon. So not only Ctrl + D duplicate the icon but also repro the offset of the shape 😲😎😍

Press Ctrl + D 8 more times and you have your progress bar

Step 6 : Group the icons

Go to the menu Home > Find & Select > Select Objects

  1. Drag & Drop to select all your icons.
  2. Toggle off the option Select Objects
  3. Go to the menu Graphics Format > Group > Group

Step 7 : Change the color

This step is not a basic one. In order to have a nice split between the shape and the outside, it's important to have a color for the shape and another color for the outline of the shape.

Change the color of the shape and the outline

Step 8 : Add a rectangle and group again

The next steps are not explain in the video and everything is done in Excel 😉

  1. Go to the menu Insert > Shape
  2. Add a rectangle
  3. Use the tool Bring Forward or Send Backward to put the progress bar above the rectangle
Menu bring forward send backward
  1. Adjust the size of the rectangle with the icon bar
  2. Change the color of the rectangle to white
  3. Group the rectangle with the icon bar

Step 9 : Convert to image

Copy the group of shapes and use the option Paste Special to Picture (gif)

This step is very important because one important option is reachable only with the picture menu.

Step 10 : Replace the color with transparency

Go to the menu Picture Format > Color > Set transparent Color

Select the color to replace by transparency

Step 11 : Conditional formatting

In B1, write a value between 0 and 100 and in A1 write this simple formula

=B1

Stay in A1 and go to the menu Home > Conditional Formatting > Data Bars

Select one of the options (here Solid Fill blue)

Step 12 : Customize the conditional formatting options

  1. Stay in the cell with the conditional formatting
  2. Go to the menu Home > Conditional Formatting > Manage Rules
  3. Edit the rule
  4. Change minimum and maximum to Number
  5. Min value = 0
  6. Max value = 100
  7. Check Show bar only
Customize the conditional formatting

The job is done 👍

Put the image of the progress bar with icons over the conditional formatting and adjust the size of A1

Progress bar with icons and conditional formatting

Tutorial Video

In the following video, you will see all the steps except the technique to convert a color with transparency. In the video, I have used SnagIt.

Related posts


Permanent link to this article: https://www.excel-exercise.com/create-a-progress-bar-with-icons/


Leave a Reply

Your email address will not be published.