« 20 Ways of Excel Pasting Techniques | 'exceltoxl' wishing you a » |
Making of an in-cell Chart
chartsHi there,
There are huge number of excel lovers in this world however, very few of them will discover new techniques using existing excel features. One among the new creation is "Making of an in-cell Chart". This chart is not an inbuilt by Microsoft however it is discovered by one among the excel lovers with us. Let's have a look on how to prepare an in-cell chart using Excel.
Let's say, I have a set of data which resembles the number of visitors that has visited our blog "exceltoxl" for past six months.
...
- REPT function
- ROUND function
- CONCATENATE function
- IF function
- CTRL + 1 (Format Cells Window)
- CHAR function
- WRAP TEXT
PREPARATION OF IN-CELL CHART:
- Use REPT function for each cell value to repeat the number of times the pipe character. (To get pipe symbol use SHIFT + \ [backward slash] )
- As we have higher values we will reduce it to within 10 pipes. So, use ROUND function in REPT to reduce the values to 10.
- The function here for December looks like this: =REPT("|",ROUND(B3/1100,1))
- Similarly, use this function for the rest of the cells in months
- The function here for June looks like this: =REPT("|",ROUND(H3/1100,1))
- Now, use CONCATENATION function to concatenate all the pipes into one cell
- Here, to divide each cell value need to use =CHAR(10), this is similar to CTRL + ENTER (entering the second line in a same cell)
- Once done use CTRL + 1 or right click from mouse and select FORMAT CELLS
- In format cells window, change the orientation to 900 and check mark the "Wrap Text" under Text Control.
- Click OK
That's it. Magic works.
The formula after using CONCATENATION function looks like this:
=K3&CHAR(10)&L3&CHAR(10)&M3&CHAR(10)&N3&CHAR(10)&O3&CHAR(10)&P3&CHAR(10)&Q3
DO YOU USE in-cell CHART?
Have you used this chart in your work. Did you find any new charts while working on your reports. Please do use this space to share your ideas, suggestions, achievements, findings with our readers by posting in the below comments section.
Do you like this post, then please do share and like this using social networking icons placed around the blog. Do you want to support me for maintaining this blog and to receive latest updates, just REGISTER here for free.
SOME IMPORTANT LINKS:
- CreateDynamicNames in Charts
- ImageCharts in Excel
- CookedDynamicCharts
- Prepare a ChartUsingConditionalFormatting
- Prepare a Gantt Before Sipping a Tea
This post is prepared with inspiration of chandoo's post.
Thanks,
You must be logged in to see the comments. Log in now!
If you have no account yet, you can register now! (It only takes a few seconds)
Recent comments