Lets start the week with a quick tip.
Lets say you have a list of employees and their birthdays. Now you want to sort this list, based on their birthday, not age. How would you do it?
Sorting by day and month alone:
- Add a column next to original dates. Lets call this Birthday.
- Then, calculate birthday in current year for everyone.
- Assuming DOB is in B1, Formula for birthday (in current year) would be,
=DATE(YEAR(TODAY()),MONTH(B2),DAY(B2)) - This formula gives you a date which has same year as TODAY(), same month & day as original date.
- Then, fill down the formula for all rows.
- Now sort this new column (Birthday) in chronological order.
- You are done!
Note: if you are using tables, then use this formula.
(Assuming original date is in DOB column),
=DATE(YEAR(TODAY()), MONTH([@DOB]),DAY([@DOB]))
Related: Introduction to Tables & Structural References.
More Sorting Examples:
- Sorting sideways
- Sorting in Olympic medals table style, Formula 1 racing style
- Rounding and sorting data
- Checking if a list is sorted using formulas
- Sorting text values using formulas
Homework for you:
If you think sorting by birthdays is easier than eating a birthday cake, then I have a challenge for you. Assuming a list of data of births is in the range A1:A100, write a formula to find how many birthdays are in this month?
Go ahead and post your answers in comments.
9 Responses to “Show forecast values in a different color with this simple trick [charting]”
While this works in a pinch, it clearly "lightens" the colors of the entire chart. Depending on where you use this, it will be blatantly obvious that you don't know what you are doing and present a poor looking graph.
Why not separate the data into different segments when charting and have as many colors as you have data points? You might have to create a new legend and/or repeat the chart in "invisible ink", but it would be cleaner and more consistent when new or updated data becomes available.
While I think I agree that doing it "properly" via a second series is preferable, I don't necessarily agree that making the entirety of the "future" (data, gridlines, and even the axis) semi-transparent is "poor looking". I think it could be seen as adding more emphasis to the "future-ness" of the forecast data.
In short, it's another tool for the toolbox, even if it's never needed.
Simply and clever 🙂
Quick & effective, cool. thanks.
I always use the dummy series.
Nice little trick, thanks very much!
Two sets of data better. Control is much better.
You can use the same chart next month to see what is actual and what is forecast.
To use this trick, I think grid lines has to be removed, that will make the graphic much more sharp.
to be honest, i dont understand why there is needed to do this way... in this case horizontal lines will be pale as well. then why a just can't change the color of the line partly???
Great tutorial. Thanks for the tutorial!