INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Jobs

Little-known Excel features and functionality

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

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

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

=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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Little-known Excel features and functionality

JG2828 - how did you get a clean animation clip of Excel showing keyed data entry, and how did you upload / post it on this thread?

RE: Little-known Excel features and functionality

ATSE,
I made a screen recording with a program called SnagIt. It creates an mp4 video file. I uploaded the the video file to an online mp4 to gif website. I think I used ezgif.com/video-to-gif. Then I posted the gif here using the Upload Image button.

RE: Little-known Excel features and functionality

I love SnagIt, but I hadn't thought of using it for animated gifs, so thanks for the example and the ezgif link.

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

RE: Little-known Excel features and functionality

IDS,
I had never made an animated gif before. I got the idea from the link in the original post, most of the examples are animated gifs and were really helpful.

RE: Little-known Excel features and functionality

Hey just came across this one by accident.
As I said earlier, holding control-; (semicolon) will insert the date into a cell. Well I just found out by accident that shift-ctrl-; will insert the time.

ctrl-; = 7/17/2017
shift-ctrl-; = 4:10 PM

This can be done while selecting a cell, or while editing text within a cell.

RE: Little-known Excel features and functionality

You can navigate to the top or bottom of a column or to the left or right of a row, by holding the Shift key, selecting the End key and then using the up/down or left/right arrow keys.

RE: Little-known Excel features and functionality

just this weekend discovered add-in "bing map"
been hunting for software for 2 years to plot my addresses automatically on a map

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.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close