Excel Tips - Share your tricks

[TouchDown]

Skywarp -

Do you have to have them hidden with a macro, or could you just select the data, autofilter (under data), then on drop down menu, you can select (custom...).

Once in custom selection, enter "does not equal" and enter 0 in the right hand block.

That should hide all zero rows.

 

[SkyWarp]

Excellent, that will work just fine. I'll still add it to a recorded macro and assign it to a picture. I anticipate more duties will be thrown at my team in the near future and I want to make sure everyone can use this little tool in the spreadsheet. Thanks.

 

[sceriana]

Here is a question for all of you. I have run into this many times in the past but never figured it out.

If I am plotting an XY-scatter graph with months on the x-axis, but I want to adjust the scale so it starts on a certain month, shows every month (or however many I want it to show) and ends on a month ... how do I do that? I ask because when I go to adjust the scale, instead of months, there are huge numbers, such as: minimum: 38040, maximum: 39800 ... how do I get it to show minimum: February 2004 ... and such? Thank you all.

 

[jroyce]

Each date in excel is given a numerical equivalent. So you can either put it the numerical equivalent (change the format of the cell to general. Or you should be able to just type in the date in the maximum or minimum scale and then it will start / stop at that date.

I think that is your question if I am reading it right.

 

[frazil]

I think with a x-y scatter plot you'll always see the number equivalent of the date when you set the max and min for the axis properties, but you can display the date on the plot by going to the "Number" tab in the format axis box and selecting a date format that you want to see.

 

[Guest]

I don't trust MS Excel when it comes to plotting time-dependent data, especially if the data does not follow a periodic time pattern. I have seen MANY people try to forecast trends with groundwater sampling data and inter-mix the quarterly and semiannual results - everything is equally spaced and skews (or screws) the analysis for attenuation.

I like to use Kaleidagraph - I had a friend turn me on to this program. Very easy to use and very powerful - especially multiple-axis data.

JR

 

[sceriana]

Each date in excel is given a numerical equivalent. So you can either put it the numerical equivalent (change the format of the cell to general. Or you should be able to just type in the date in the maximum or minimum scale and then it will start / stop at that date. I think that is your question if I am reading it right.

I think with a x-y scatter plot you'll always see the number equivalent of the date when you set the max and min for the axis properties, but you can display the date on the plot by going to the "Number" tab in the format axis box and selecting a date format that you want to see.

Yes, I kind of sort of knew that trick, changing back to the numerical equivalent but I was hoping to avoid that. Unfortunately by typing in the date into min/max and such, it does not seem to like that very much at all. I guess it is a bit frustrating to take the extra step of figuring out the min/max by numerical equivalents instead of just seeing the month. And having my actual table with the months as opposed to numerical equivalents, since if I want to print out the table. I am just being lazy and I remember once or twice working with an excel table that didn't show numerical equivalents but the actual months in the scale tab ... but I have no idea how that came about. Thanks for the help jroyce and fraz!

I don't trust MS Excel when it comes to plotting time-dependent data, especially if the data does not follow a periodic time pattern. I have seen MANY people try to forecast trends with groundwater sampling data and inter-mix the quarterly and semiannual results - everything is equally spaced and skews (or screws) the analysis for attenuation.I like to use Kaleidagraph - I had a friend turn me on to this program. Very easy to use and very powerful - especially multiple-axis data.

JR

Kaleidagraph ... never heard of it. I will have to check it out. My curiosity has definitely peaked. Thanks JR!

 

[frazil]

I think with a line plot you type the actual data into the max/min. You can change the chart type and then format the line to it's just dots - not connected.

 

[MA_PE]

Yes, I kind of sort of knew that trick, changing back to the numerical equivalent but I was hoping to avoid that. Unfortunately by typing in the date into min/max and such, it does not seem to like that very much at all. I guess it is a bit frustrating to take the extra step of figuring out the min/max by numerical equivalents instead of just seeing the month. And having my actual table with the months as opposed to numerical equivalents, since if I want to print out the table. I am just being lazy and I remember once or twice working with an excel table that didn't show numerical equivalents but the actual months in the scale tab ... but I have no idea how that came about. Thanks for the help jroyce and fraz!Kaleidagraph ... never heard of it. I will have to check it out. My curiosity has definitely peaked. Thanks JR!

As far as using dates on an X-Y plot you need to enter the start and end date in Julian format (1 = 01/01/1900) to get the numerical equivalent of a given date type the date into a cell (as a date) and then copy and then <paste special> <value> into another cell. You will see the coresponding number. The display on the graph can be cahngeda by formatting the axis as fraz said above. Unfortunately, to get a correct timeline in excel you need to use an X-Y plot and the scale range MUST be entered integer fashion.

Another note is that integer dates represent a time of 12:00 AM if you need to break it down to time of day the "time" equivalent is a fraction of the day (i.e. 0.5=12:00PM)

hope this helps.

 

[sceriana]

As far as using dates on an X-Y plot you need to enter the start and end date in Julian format (1 = 01/01/1900) to get the numerical equivalent of a given date type the date into a cell (as a date) and then copy and then <paste special> <value> into another cell. You will see the coresponding number. The display on the graph can be cahngeda by formatting the axis as fraz said above. Unfortunately, to get a correct timeline in excel you need to use an X-Y plot and the scale range MUST be entered integer fashion.
Another note is that integer dates represent a time of 12:00 AM if you need to break it down to time of day the "time" equivalent is a fraction of the day (i.e. 0.5=12:00PM)

hope this helps.

Sorry for the late reply, been crazy in the office these past two days.

But thank you for info MA_PE. Definitely know much more now than I did before. It is tricky playing with excel but it all works out in the end.

Thanks again, very much appreciated!

 

[grover]

I recently discovered the joys of "custom" cells format that lets you alter the color or formatting. the way it looks, insert text vice a number, etc. Makes it even easier than if-then statements. I haven't quite figured out all the specifics of it, but if you go into cell-formatting, where you normally chose number/text/currency/percentage, etc- go down to custom and scroll through the list. It's split up into +,-,0 with different formatting possible for each contingency, including text. I haven't figured out precisely how the formatting works, but you all can play around with it

For instance:

[black]# Remaining,[Red]# overdraft,[Green]Balanced

...would return exactly what it looks like it would.