Discover how you can create a progress bar with icons in your worksheet.
Table of Contents
Step 1 : Import one icon
Go to the menu Insert > Icon
Select one of the icons and click on Insert
Step 2 : Crop the icon.
Select the icon and go to the menu Graphics Format > Crop
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.
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
- Drag & Drop to select all your icons.
- Toggle off the option Select Objects
- 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.
Step 8 : Add a rectangle and group again
The next steps are not explain in the video and everything is done in Excel 😉
- Go to the menu Insert > Shape
- Add a rectangle
- Use the tool Bring Forward or Send Backward to put the progress bar above the rectangle
- Adjust the size of the rectangle with the icon bar
- Change the color of the rectangle to white
- 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
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
- Stay in the cell with the conditional formatting
- Go to the menu Home > Conditional Formatting > Manage Rules
- Edit the rule
- Change minimum and maximum to Number
- Min value = 0
- Max value = 100
- Check Show bar only
The job is done 👍
Put the image of the progress bar with icons over the conditional formatting and adjust the size of A1
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.
- Understand Standard Deviation
- Round to the Nearest Ten, Hundred, Multiple..
- Mortgage calculator with Excel
- First day – Last day in Excel
- Displaying Negative Numbers in Parentheses – Excel
- Time Formats in Excel
- Inspect a Formula with F9
- Convert Latitude and Longitude
- What is a Logical Test in Excel
- The Correct Value for a Week Number