Posts Tagged ‘Excel tricks’
Quote of the day
To become aware of the possibility of the search is to be onto something.
Natasha wanted to graph photocopier downtimes to show the duration of each downtime in minutes with data points labeled with the date of the down time incident. Her data list included the date of the copier needed service and the number of minutes it remained out of order. This seems simple enough but when she tried to generate a graph for the year, every day of the year appeared in the graph. That wasn’t quite what Natasha was looking for. She wanted to show only dates when there was copier downtime.
Graphing, it seems, assumes that if a series consists of date values then you want a date series showing every date between the first and last dates. Here you see the problem in the graph on the left, and a solution in the graph on the right. In the original data, the first entry is February 12 and the last, December 14. Notice how the graph is display dates from February 12 to December 12.
Because of the size of the graph, the labels display only one date for each of the eleven months.
The graph on the right, on the other hand, has data points only for each of the dates in its data series. (The size of the graph may cause alternate data points to be un-labeled, so the graph might need to be widened somewhat so that each data point has a label. You may also have to format the axis.) Because the X-Axis data is self-explanatory, I have removed the legend from both graphs.
The secret, to paraphrase Fried Green Tomatoes, is in the formula. Actually, it is in a function that the formula uses. TEXT() is a very versatile function, well worth adding to your Excel arsenal
In this example, I have used TEXT() to convert the date values in column B to text values that include the name of the weekday on which the date occurs.
The TEXT() function converts numeric values to text . It takes two arguments, a reference to a cell containing a numeric value and a string to indicate how the text should be formatted.
For example, the formula in cell J3 is:
=TEXT(B3, “ddd mmm dd”)
The format string here specifies that the text should begin with the three character abbreviation for the day name followed by a space, the three character abbreviation for the month name, another space, and finally a two digit number for the day.
There are many variations on the format string. Search the Excel help files for Text Functions or point your browser to Office On-line. The help article gives full details of how to specify the format string (the second argument of the function) depending on the numeric value you are trying to convert to text.
The point in this example is that by converting the dates in column B to text, and using the data in column J for the series labels, we can fool Excel into displaying date without having it automatically treating the dates a continuous series from the earliest date to the latest date.
Thanks Natasha for raising the question.
Quote of the Day:
Copy from one, it’s plagiarism; copy from two, it’s research.
I tried a little experiment yesterday and was pleasantly surprised by the results. A while back I had learned how to link a chart title to data stored in an Excel worksheet. That in itself is an enhancement to charting.
If you have done any charting (graphing) in Excel, you will know that most of the chart’s values are stored in a worksheet. Change the worksheet values, and the chart will automatically update. Here’s an example. Let’s say that I am using a worksheet to prepare a monthly sales presentation to management. Management want to see the relative contribution of each salesperson to year to date sales. So I have set up a simple pie chart to display the information. Obviously, I have much more to do in my job than just to prepare this once a month presentation so I want to produce this chart as efficiently as possible.
Now, when I created the chart, using the chart wizard, I had to ‘hard code’ the title. That means that each month after I have updated the underlying date, I have to manually edit the chart title so that it reflect the reporting month. That takes a minute or two and for me it’s easy to forget. Forgetting to change the title costs more time. I have to re-open the file and make the change. Notice how the chart title at the moment is simply, “YTD Sales,” not reflecting the fiscal period.
To keep management of this chart as simple and efficient as possible I want to enter data only into the five cells that are highlighted in yellow. Of course the sales figure cells could actually be getting their values from formulas referring to the raw data. Then all I would have to do is enter the period ending date (and even that cell could contain a formula referring to the raw data.)
Here I have made one small change. The textbox containing the chart title no longer contains literal text. I have replaced the literal text with a formula that refers to a cell containing the title exactly as I would like to see it in the chart. Now, regardless of the reporting date, the chart will always indicate the correct date.
These examples show an embedded chart. Having a date specific title is not as critical in embedded charts as it is in charts that are rendered on separate chart sheets in the workbook. In those cases, you can’t just glance at the data to see the reporting date applicable to the chart without moving to the other worksheet.
The formulas revealed
The next graphic exposes the formulas involved. I have switched the worksheet to display formulas rather than the values generated by those formulas using ctrl+`. For this example, the sales results are simply random numbers because I don’t have any real data that can be published. The chart title text box contains a formula as well but it is still displaying the value rather than the actual formula which, in this case, is =$D$4.
Cell D4 itself contains a formula that creates and formats the text I would like as the chart title.
Now you ask, why would I want to take the time to set this up when I could simply edit the title every month? For me it’s a no brainer. Yes, it took some time to set up – probably less than five minutes, to be honest with you. What is the payback on that investment of my time. Sure it is small but let’s say it takes me 1-1/2 to 2 minutes each month to edit the title. In the first three months I have more than recovered my time investment.Then there is the intangible benefit of saving me the embarrassment of forgetting to change the title or occasionally misspelling something in the title. Besides, I just think this is a cool possibility.
The is just one small thing to remember when you want to try this yourself. Ordinarily, when you want to enter a formula you select a cell and then type the formula in either the cell or the formula bar. When you want to enter a formula in a chart title text box, select the textbox but do not type anything in it; when you have the textbox selected, type the formula in the formula bar.