Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
Leveraging the REPT() function and a special font in order to build charts in cells via formulas
Download this lesson’s related exercise files.In-cell charts using the REPT() function.xlsx
74.2 KB In-cell charts using the REPT() function - Completed.xlsx
In-Cell Charts Using The REPT() Function
Building in-cell charts by repeating characters x numbers of times.
When to use
When you want to build attractive progress bars using formulas rather than other methods.
Lay the groundwork
- Before you start, figure out how long you want your progress bar to be. (I’ve found 10 characters to be a good number)
- Create a cell that shows the progress to your goal, returning a value that you can easily convert to a scale of 1 to 10
Get the Symbol Needed
- Go to Insert --> Symbol (far right) and choose a Wingdings fonts (Wingdings2 has the squares)
- Locate the character you want and click Insert to insert it in the spreadsheet, then click Cancel
- Your formula will be in edit mode, so enclose the character in the REPT function as follows:
- This will repeat the character the number of times specified, building a chart
Adding additional symbols
- The trick is to get the character you need, as it often isn’t on your keyboard
- Select another cell in the worksheet and insert your symbol
- When you return to the worksheet, copy the required character from the formula bar
- Modify your original format, adding another REPT() function using the & key and paste in your character
Hints & tips
- Once you’ve inserted a character once, it will show up in your Recently Used Symbols list
- Any character can be used that makes sense to you
- It is a good idea to place logic in your values to ensure that you never end up with an error in your formula, or you will see some very odd results!
- To jump to the black square, choose the WingDings 2 font and enter Character Code 162
- To jump to the white square, choose the WingDings 2 font and enter Character Code 163
Lesson notes are only available for subscribers.