Sep 06

Excel Axis Labels from Two Columns

Say you have two columns of data, like the day of the week and the date, that you’d like to concatenate together as the X-axis labels on a chart. You could make a new column with a formula that concatenates them together and use that column for the X-axis, but I recently discovered, completely by accident, that Excel will do it for you! Simply set the Horizontal Axis Labels in your chart’s Select Data pane to a range from the top left cell to the bottom right cell of the columns you want to concatenate:

=Sheet1!$A$2:$B$20

Oct 17

Querying Outlook

One of my developers is proposing to put the text of every error email that the web application generates into the database because they contain debug information. I’m not certain that anyone will use this information, so I wanted to estimate how space this was going to take up. I had a bunch of the error emails in my mailbox, and I discovered that if you highlight a bunch of messages, copy, and paste into Excel, you get the same grid. The sizes are strings (“34 KB”), but you can then use a formula to isolate the number (“=INT(LEFT(D2, LEN(D2)-3))”) and use the AVERAGE function on them.