Let’s say you made a chart to show actual and forecast values. By default, both values look in same color. But we would like to separate forecast values by showing them in another color.
If you are a seasoned Excel user, you may be thinking, “Oh, that’s easy. I will just create 2 sets of data (one for actual and one for forecast), make a chart from them and apply separate colors.”
But here is a really simple way to get the same effect.
Use a semi-transparent box to mask the forecast values. The end result is shown below.
Here is how the trick works:
- Create the chart from all values.
- Draw a rectangle (box) shape on your spreadsheet.
- Fill it with white color and remove outline (set the outline color to no line).
- Select the box, Go to Fill > more colors and set it to 50% transparent.
- Place the box on top of chart, adjust its size and position to overlap the forecast data.
- Your forecast looks in a different color!
See below demo to understand the process:
Learn more about forecasting
If your work involves trend analysis & forecasting, check out below resources:
- Introduction to trend analysis in Excel – podcast
- Doing trend analysis & forecasting in Excel – 3 part series
- How to highlight best months & weeks in charts
How do you highlight your forecasts?
My personal favorite is to use dotted lines to separate forecasts. This involves either using Excel’s chart trendline option or adding a dummy series thru formulas to show the forecast line. When I am in a hurry, I usually add a semi-transparent mask to set aside the forecast values.
What about you? How do you highlight forecast values in your charts? Please post your technique in the comments area.
11 Responses to “Use Alt+Enter to get multiple lines in a cell [spreadcheats]”
@Chandoo:
One more useful trick.......
In a column you have no. of data in rows and need to copy in the next row from the previous row, no need to go for the previous rows but entering Alt + down arrow, you will get the list of data, (in asending order), entered in the previous rows...
This is another great tip. I use this all the time to make sense of some *very* long formulas. As soon as the formula is debugged I remove the break.
Great tip Chandoo!
I use this feature often and it has even gotten the, "how did you do that" response.
Thanks!
@Ketan: Alt+down arrow is an awesome tip. I never knew it and now I am using it everyday.
@Jorge, Tony: Agree... 🙂
[...] Day 1: Insert Line Breaks in a Cell [...]
how can we merge a two sheet.
excellent idea. Chandoo you are genious
Hi chandoo,
I have used ctrl+enter to break the cell. But I did not get the result.
Please tell me how can i break the cell in multiple lines.
Hi, Ranveer,
Its not Ctrl+enter to break the cell, use Alt+Enter to make it happen.
hi Chandoo....
how we can use Alt+Enter in multiple rows at the same time please reply hurry i have lot of work and have no time and i m stuck in this. 🙁
Alt+J worked once 🙁
So I found another more reliable way:
=SUBSTITUTE(A2,CHAR(13),"")
Where A2 is the cell that contains the line breaks which the code for it is CHAR(13). It will replace it with whatever inside the ""