# Excel Tips - Share your tricks



## TouchDown (May 22, 2007)

Quick keys -

To highlight a "block" of data from top to bottom - (even if it's 15000 rows long)

click on a corner of the block (example upper left). Hold down CTRL+SHIFT, then hit right arrow, then hit down arrow. It will select total block. Things that might trip it up, is it's looking for next blank, so if column your selecting across isn't complete data, it'll just to until the next blank.


----------



## TouchDown (May 22, 2007)

Does anyone remember the flight simulator that was packaged in the background, I was thinking it was XL, not positive? You pressed a certain few keys and it popped up a flight simulator. They might have eliminated it in newer rev's though.


----------



## Tiger (May 22, 2007)

I probably use Goal Seek more than any other feature in Excel. Its under Tools, Goal Seek.


----------



## MA_PE (May 22, 2007)

I use the Regression feature of the Data Analysis Too Pak quite a bit.

Superposition of the curve fit line over plotted data is a nice feature too.


----------



## jeb6294 (May 22, 2007)

TouchDown said:


> Does anyone remember the flight simulator that was packaged in the background, I was thinking it was XL, not positive? You pressed a certain few keys and it popped up a flight simulator. They might have eliminated it in newer rev's though.


Yeah I remember that...I think that it is just in MS Excel 2000 though.


----------



## Ken (May 25, 2007)

Got so many tips and tricks that I can't list them all. If anyone has a question relating to excel, post them here or send me a PM, I will try and respond ASAP, usually the same day if I am not too busy.

There are quite a few ways to make your spreadsheets more robust and efficient, gald to help out.

Ken


----------



## Ken (May 25, 2007)

Ken said:


> Got so many tips and tricks that I can't list them all. If anyone has a question relating to excel, post them here or send me a PM, I will try and respond ASAP, usually the same day if I am not too busy. There are quite a few ways to make your spreadsheets more robust and efficient, gald to help out.
> 
> Ken



For starters, if you use Excel a lot, download this add-in feature, it will make your life much more easier.

www.asap-utilities.com/download-asap-utilities.php


----------



## FlyPaper (May 26, 2007)

It was Excel 97, here is how to do it.

Restart Excel 97 .

Open a new blank document .

Press F5 .

A reference box will open , type in X97:L97 and press ENTER .

Press TAB .This will take you to the cell M97 .

Click on the chart wizard button while holding down Control and Shift keys .


----------



## JoeBoone82 (May 31, 2007)

Excel is very powerful.... I can find some use for it on almost everything. I wish I knew more of it than I do. Someone once told me about a guy who had his Excel sheet linked with the stock market.... and that he had graphs and charts that would change continuously with his stocks. Is that possible? That's more than I could do... that's for sure.


----------



## ktulu (Jun 7, 2007)

Don't have any tips. Actually looking for some help. Nothing as complicated as frazil's counting program. Probably elementary, but I cannot figure it out. I am trying to freeze both a row and a column at the same time. I know it can be done (I actually have it done in another file, but now I can't figure out how I did it...).

i.e. Freezing both Row 1 &amp; Column A

Thanks,

ktulu


----------



## Guest (Jun 7, 2007)

ktulu --

You have asked an Excel question I can answer !!!! 

In order to freeze Column 1 and Row A:

Highlight Cells A2 and B2, then from the menu bar, select Window --&gt; Freeze Panes. That will freeze both the Column 1 and Row A.

In general, to freeze a pane, do one of the following:

*The top horizontal pane *Select the row below where you want the split to appear.

*The left vertical pane *Select the column to the right of where you want the split to appear.

*Both the upper and left panes *Click the cell below and to the right of where you want the split to appear.

Once you have selected your cells (panes), on the Window menu, click Freeze Panes

JR


----------



## ktulu (Jun 8, 2007)

Thanks, JR

My wife (who actaully the question was for) was playing around with it last night and ended up figuring it out.

ktulu


----------



## Ritchie503 (Jun 14, 2007)

ktulu said:


> I am trying to freeze both a row and a column at the same time. I know it can be done (I actually have it done in another file, but now I can't figure out how I did it...).


I had to look that up in the online help a couple months ago... Real kicker was I one column/row click off of doing it right, but had to break down and read instructions.


----------



## frazil (Jun 14, 2007)

I have a question too for all you excel gurus: I want to change the default graphs so they come up the same every time and so I don't always have to edit everything. Stuff like gridlines, on and what line type, etc. Is there a way to do that?


----------



## TouchDown (Jun 19, 2007)

If you modify an existing chart and want to use it as a "style" for future charts... From the XL help... haven't done this myself - *****************

Microsoft Excel provides a variety of standard and built-in custom chart types. If needed, however, you can turn any chart type that you have modified when you created a chart into a user-defined custom chart type that you can use again or share with other users.

Do one of the following:

To save a chart that you modified as a custom chart type, click the chart sheet (chart sheet: A sheet in a workbook that contains only a chart. A chart sheet is beneficial when you want to view a chart or a PivotChart report separately from worksheet data or a PivotTable report.) or the chart area (chart area: The entire chart and all its elements.) of that chart.

On the Chart menu, click Chart Type Tab.

On the Custom Types tab, under Select from, *click User-defined*.

Do one of the following:

To add a custom chart type, click Add, and then type a name in the Name box.

If you want to add a description, type it in the Description box.

To delete a custom chart type, click it in the Chart Type box, and then click Delete.

Note You cannot delete a built-in custom chart type.

Click OK.


----------



## TouchDown (Jun 27, 2007)

Any luck Frazil?


----------



## frazil (Jun 28, 2007)

Ooh...I just saw this TD. I'm on travel now, but I'll give it a try when I get back to my office. Thanks!!


----------



## frazil (Jul 12, 2007)

TD - I just tried this for the first time. I created a user-defined plot last time I created a graph and then selected it as the type this time and it came out perfect. That was easy! Thanks!!


----------



## RVincent (Jul 12, 2007)

I have gotten a lot of use out of the Macro Record function. There have been many situations where I have needed to compile test data that required importing, copying, pasting, formatting, etc over and over again. Going through the sequence of operations once with the Macro Recording function on, sped up the data reduction process dramatically. Give it a try!


----------



## SkyWarp (Jul 15, 2007)

I have a spreadsheet and I'd like to have a macro that hides all rows that have "0" in column B. I played around with some codes written in VB but I couldn't tweak them to work for my spreadsheet. Can anyone point me in the right direction?


----------



## TouchDown (Jul 16, 2007)

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 (Jul 16, 2007)

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 (Aug 15, 2007)

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 (Aug 15, 2007)

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 (Aug 15, 2007)

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 (Aug 15, 2007)

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 (Aug 15, 2007)

jroyce said:


> 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 said:


> 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!



jregieng said:


> 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 (Aug 15, 2007)

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 (Aug 15, 2007)

sceriana said:


> 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 &lt;paste special&gt; &lt;value&gt; 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 (Aug 17, 2007)

MA_PE said:


> 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 &lt;paste special&gt; &lt;value&gt; 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 (Aug 23, 2007)

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.


----------

