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!
Tunalover
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
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
Dik
RE: Little-known Excel features and functionality
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
RE: Little-known Excel features and functionality
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
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,
for a NUance!
RE: Little-known Excel features and functionality
RE: Little-known Excel features and functionality
Dik
RE: Little-known Excel features and functionality
RE: Little-known Excel features and functionality
RE: Little-known Excel features and functionality
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
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
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
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Little-known Excel features and functionality
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
RE: Little-known Excel features and functionality
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
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
There were and, I think, still are, some Quattro Pro diehards...
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
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
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
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,
for a NUance!
RE: Little-known Excel features and functionality
Transform Data by Example
Also the DDoE post that led me to it has a video on Power-BI, and linking to Excel, which I guess is also still pretty little-known.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Little-known Excel features and functionality
RE: Little-known Excel features and functionality
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
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
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Little-known Excel features and functionality
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
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
RE: Little-known Excel features and functionality
been hunting for software for 2 years to plot my addresses automatically on a map