Little-known Excel features and functionality 6

[Tunalover]

Folks:
Earlier this weekend I encountered an article from a group in LinkedIn that provided me, a 30+ year Excel user, with pleasantly surprising features and functionality. I am attaching these for your reference. Credit goes to the author clearly shown on the webpage.Link

I gave up on the attachment!

Tunalover

 

[IDS]

Turns lap-top over.

No, can't see an attachment anywhere.

(TIA for re-post)

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[dik]

Do you remember the old 'Easter Egg' flight simulator?

Dik

 

[IFRs]

Aside from F4 I use the rest all the time. Good list !!

 

[SkipVought]

3 additional neat Excel features

The Camera feature is a convienient DISPLAY feature that can graphically display cells in a reference range in a totally diffent range or sheet. There is a Camera icon that can be displayed in your Quick Action Toolbar (QAT).

The Current Region (QAT icon) selects the current region contiguous with the active cell.

The SUMPRODUCT() function can function like the SUMIFS() or COUNTIFS() functions, but with a far more intuitive syntax like...
[tt]
=SUMPRODUCT(range1=value1)*(range2<=value2)*(num_range3))
[/tt]
...where num_range3 is summed based on these two critera in range1 and range2.

...or like...
[tt]
=SUMPRODUCT(range1=value1)*(range2<=value2))
[/tt]
...the count of rows based on these two criteria in range 1 and range 2.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[IFRs]

SubTotal is an awesome but little used one also

 

[JG2828]

Thanks for the link. Those are helpful. Here are a few I use regularly.

CTRL ; is very handy to insert the current date into a cell. ie. 6/7/2017 Much better than the formula =today() because it will not auto update every time you open the excel file.

CTRL ` (top left of keyboard above tab) toggles "show formulas" on and off. This is useful to verify that there are formulas where they should be and someone didn't overwrite them with a number. I have been burned by that before.

 

[SkipVought]

Actually SUBTOTAL() has TWO sets of Function numbers:
1-11 aggregates hidden rows
101-111 aggregates only visible rows -- Handy for aggregating in filtered tables.

so
[tt]
=SUBTOTAL([highlight #FCE94F]9[/highlight],range)
[/tt]
...will TOTAL all cells (visible & hidden) in the range while...
[tt]
=SUBTOTAL([highlight #FCE94F]109[/highlight],range)
[/tt]
...will TOTAL only visible cells in the range.




Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[oldrunner]

Just saw this thread. Really very good. Started spread sheets with Visacalc on a TRS80 Model 2. Some of these shortcuts I use with a purchased add-in - but I am still using Excel 2003. Routinely do my calcs similar to manual calcs but hyperlink to the index page so that I can jump all over the many pages in the file. I like all numbers visible and to be able manually check if possible. Sometimes takes longer but if I come back to the file in a year or so - I can still follow the thinking.

 

[dik]

I still have my old 5-1/4 disc for Visicalc for an original PC XT... don't have any means/need to load it...

Dik

 

[davidbeach]

For far longer than I had access to any means of reading it, I had a tape of all of my PDP11 greatest hits...

 

[IFRs]

I still have a box of TI magnetic cards with repetitive calculations, 5 1/4 and 3 1/2 floppy disks with next gen coding first for the Apple and then IBM PC computers and although I've tossed the punch card stacks for Lehigh U's "mainframe" I still have the electronic copies of the input and output decks. Damn I'm old!

 

[davidbeach]

Cards... you're ancient. ;-)

I found that with the right shiv like tool there would always be an available key punch machine available in that room. All I ever had to do was look around for the key punch nobody was using, bypass the line of waiting "users" and go straight to the unused machine. It never took more than a couple of minutes to clear the jamb and have a fully functional machine with no waiting in line.

 

[oldrunner]

Used to use CDC in San Jose to run our batches. Go down there at night for the runs. Make the corrections and then wait about 30 minutes for another trial. Awful coffee.

First computer was a Wang 600 ($3300). Really a large programical calculator, but I bought quite few routines that made our work a lot easier. Use to paste the ribbon output on the calc sheet with the rest of the input info, then copied the sheet with our new Xerox machine. Calcs package came with all of the documentation that we would submit to the building department.

The Radio Shack TRS-80 Mod II had 7 inch floppies. I bought SAP 80 that ran on the CPM operating system which the TRS would work with. Bought the program directly from Ed Wilson for about $600. Remembered that I required a demonstration from Ed before I then bought the computer. Eventually bought about 5 TRS 80's and had a computer on all my engineers desks. Later all replaced with the XT's running DOS and moved up to Lotus 123. Also moved up to SAP 90. All my engineers prepared their own drawings - which in the long run was very helpful.

Also still have a TI51 with the memory cards. Remember best use then was for the long aluminum formulas. Still had to paste the ribbon output on the calc sheets.

 

[IRstuff]

I've punched one card, ever, because someone pointed me to RJE (remote job entry) for the IBM from the PDP10 that had VDUs and TECO.

However, I've had plenty of experience with punched TAPE for an MD104 memory tester, that had a Nixie tube display and could be programmed, one microcode instruction word at a time, from the neon-lit buttons. The punch tape could be generated from an ASR33, so one had to construct the microcode somewhere else, convert it to the equivalent EBCDIC characters, punch the tape, put the tape in the MD104 and voila! If you screwed up and mistyped, you started over again.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[SkipVought]

For all you fellow dinosaurs out there, check out The Thing King

http://duartes.org/gustavo/blog/post/the-thing-king/

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Sparweb]

Back to Visicalc 2000, aka Excel...

The "F4" locking of cells will be handy, thank you!
More powerful than that is "Named range" values.
You can give a cell a useful name such as "g", put 9.81 into that cell, and then everywhere else in the spreadsheet you can use "g" as that value.

STF

 

[IFRs]

I was a real fan of the IBM OS/2 spreadsheet program that kicked 123 and Excel called Mesa - it had some very nice features like instead of copy a formula down a column, use the formula in the top row for selected subsequent rows - if you changed the formula in the top cell it changed all the others without having to copy them down. Ahh, the good old days!!!

 

[IRstuff]

As discussed in the OP's link, Excel has a feature to fill down, by double clicking the lower right corner of the cell.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[IFRs]

Fill down copies the first cell to those under it.
Mesa created a link to the formula in the topmost cell.
The subsequent cells did not have a formula but a link that essentially said "use the formula in the first cell but update the cell references unless preceded by a $"
Otherwise it did the same thing.
Just made checking and editing much easier...