INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Little-known Excel features and functionality5

## Little-known Excel features and functionality

(OP)
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

### RE: Little-known Excel features and functionality

Turns lap-top over.

No, can't see an attachment anywhere.

(TIA for re-post)

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: Little-known Excel features and functionality

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

Dik

### RE: Little-known Excel features and functionality

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

### RE: Little-known Excel features and functionality

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

=SUMPRODUCT(range1=value1)*(range2<=value2)*(num_range3))

...where num_range3 is summed based on these two critera in range1 and range2.

...or like...

=SUMPRODUCT(range1=value1)*(range2<=value2))

...the count of rows based on these two criteria in range 1 and range 2.

Skip,

Just traded in my OLD subtlety...
for a NUance!

### RE: Little-known Excel features and functionality

SubTotal is an awesome but little used one also

### RE: Little-known Excel features and functionality

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.

### RE: Little-known Excel features and functionality

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

=SUBTOTAL(9,range)

...will TOTAL all cells (visible & hidden) in the range while...

=SUBTOTAL(109,range)

...will TOTAL only visible cells in the range.

Skip,

Just traded in my OLD subtlety...
for a NUance!

### RE: Little-known Excel features and functionality

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.

### RE: Little-known Excel features and functionality

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

### RE: Little-known Excel features and functionality

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!

### RE: Little-known Excel features and functionality

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.

### RE: Little-known Excel features and functionality

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.

### RE: Little-known Excel features and functionality

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: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm

### RE: Little-known Excel features and functionality

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

### RE: Little-known Excel features and functionality

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

### RE: Little-known Excel features and functionality

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

### RE: Little-known Excel features and functionality

If you select just one cell and drag the + sign at the bottom right down, it will copy that 1 cell down.
If you select multiple cells and drag the + sign at the bottom right down, it will figure out the sequence and fill down.
I attempted to make an animated gif of this, hopefully it works.

### RE: Little-known Excel features and functionality

If you are doing this adjacent to an existing column, you can double click on the lower right corner of the selection and it will fill down, so you don't need to find the bottom of the column.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm

### RE: Little-known Excel features and functionality

FILL HANDLE (selection lower right-hand square) method to get the first of each month example:

1) Enter Jan 2016

2) SELECT that cell, grab the fill handle and drag down or to the right (or even up or to the left).

The result will have a display format of mmm-yy, date values 1/1/2016, 2/1/2016 etc.

Skip,

Just traded in my OLD subtlety...
for a NUance!

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

#### Resources

eBook: Model-Based Definition (MBD)
As product development becomes increasingly complex and dynamic, itâ€™s necessary for traditional engineering practices, processes, tools and mindsets to adapt and take advantage of new possibilities. Download Now

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!