Excel Tips - Share your tricks

[TouchDown]

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]

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]

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

 

[MA_PE]

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]

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]

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]

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]

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]

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]

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 & Column A

Thanks,

ktulu

 

[Guest]

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

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]

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]

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]

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]

Any luck Frazil?

 

[frazil]

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]

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]

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]

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?