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

 

[IRstuff]

Excel does have an "array formula" construct that's similar.

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 forum1529 Entire Forum list

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

 

[JG2828]

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.

 

[IRstuff]

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 forum1529 Entire Forum list

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[IDS]

Just discovered this via Daily-Dose-of-Excel:

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/

 

[ATSE]

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?

 

[IRstuff]

If you do a View Source, you can see the graphic is a GIF

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

 

[JG2828]

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.

 

[IDS]

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/

 

[JG2828]

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.

 

[JG2828]

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.

 

[spciesla]

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.

 

[Triangled]

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