I just finished four days of DataPig workshops with Jon Peltier and Dick Kusleika. As always, everyone who attended had a blast and learned a ton of new tips and tricks. If you’ve never been to one, you’re seriously missing out.
.
Here’s an example of why I love these events.
Jon Peltier showed us a new trick that allows you to apply a carriage return in a custom date format. I have no idea how he figured this out, but it is damned cool.
.
Check out this screenshot. Notice how the dates are formatted so that the Month is over the Year. Now look at the formula bar. The values are actually dates.
.
Here’s how you do it:
First, enter a series of dates.
.
Next, highlight the dates and activate the Format Cells dialog box. There, you will click on Custom.
.
In the Type input box, enter
mmmm then press Ctrl+J then enter yyyy.
.
At this point, the dialog box doesn’t look all that special. But you will notice that the yyyy portion of your custom format is hidden.
.
When you press OK, you’ll get this formatting.
.
Now all you have to do is Wrap Text.
.
There you go – another trick I learned from Jon Peltier at the Excel Dashboards Bootcamp.
.
P.S. – We have only one more live workshop this year in September.
.
Well, I’m off to a relaxing three-day weekend of grilled meat and carbs.
If you try to shrink the columnwidth, you’ll notice you’ll see ####’s way before you (or I) think you should.
But a slight change can fix this.
On the alignment tab of the format cell dialog, turn off wraptext (if it’s on). Turn on “shrink to fit”, then turn on wraptext.
ps. You’ll have to adjust the rowheight manually if you autofit the rowheight.
Ha, beat you. I tweeted this Tuesday night from the airport.
An alternative to using CTRL+J is to hold down the ALT key and type the 0010 on the Number Key Pad (you cannot get this to work if you type the number from the main keyboard… only the Number Key Pad). In case you don’t recognize it, 10 is the ASCII code for the Line Feed character (the number you type in must be 4-digits long and zero front filled if the number is shorter than 4 digits). You can use this entry method to enter any ASCII character including those with no direct keyboard keyboard equivalents.
It’s good to know the years, months, days, hours, minutes and seconds elapsed from birth with this formula:
=NOW()-DATE(1957,7,17)-31
This custom cell format does the work:
yy “years”
mm “months”
dd “days”
hh “hours”
mm “minutes”
ss “seconds”
Press Ctrl+J for each new Line Feed.
great post.
I tried to format category axis labels on a chart using this technique. I wanted to format just chart labels and keep the source cells in the original format. It didn’t work. Dialog just didn’t take this custom format.
The work around was to format source range, check “Linked to Source” check box in the format Axis dialog and then uncheck it. At this point chart axis labels got formatted as desired and the source cells can be restored to the original format not affecting chart labels.
Where are you these days? Mike?
This is something (including the shrink to fit part from Dave) that I’ve wanted to be able to do for years. Thanks to all!
Hi,
this is exactly what i want to achieve for a chart X axes however after i hit enter the cell with the date just displays ” january(carriage return symbol)2012 “. in other words it seems to accept the carriage return as text rather than acting on it.
Wrap text just shows the january.
Have you gents any ideas where im going wrong?
Many thanks!
Neil
Neil: Turn on Wrap Text. This will get rid of the symbol.
>> Wrap text just shows the january
@Neil, try making the height for the row the cell is on taller.. the wrapped text should be there, but I think it is below the cell’s bottom border.
Hi,
Yes i got it to work now by reading the instructions properly!
thanks anyway