Learning to use Excel

jlaws said:
Any of these resonate with what you would need to do in the workplace...

1. Navigation using the keyboard (combinations of CTRL, arrow keys, home and end);
2. Switching worksheets CTRL+pg Up/pg Down
3. Selecting text using Shift and arrow keys (or CTRL for large blocks)
4. CTRL + A select all
5. Shift + Space - select row
6. CTRL + Space - select column
7. Shift+CTRL+ + - insert row/column
8. CTRL + - delete column
9. Change number format - ALT + H - n
10. Change to percent ALT+H - P
11. Auto fit column ALT+H - O - I
12. Edit cell - F2
13. Goto cell - F5
14. Add $ sign F4
15. Functions Sum, Max, Index, Match, Sumif, Sumifs, countif, countifs, len, left, right, find and Rank.
16. CTRL+C, CTRL + V for copy and pasting
17. Embedding one function within another.

1. Insert new sheet - ALT H I S or SHIFT+F11
2. Autosizing to avoid columns being too narrow. CTRL + A to select everything then ALT H O I
3. Deleting duplicate values - ALT A M
4. Sorting ALT A SA
5. Data filter ALT A T
6. Copying right CTRL+R
7. Copying down CTRL+D
8. New functions TYPE and VALUE
9. New functions AVERAGEIF, MAXIFS, MINIFS
10 New functions ISNUMBER and IF
11.New functions TODAY(), NOW, ROUNDDOWN, YEARFRAC, DATE, YEAR, MONTH, DAY, DATEDIF.
12. Referencing values on a different sheet WSName!Cellref or if the worksheet name has spaces 'WS Name'!Cellref.
13. Paste Special/values - ALT H V V
14. Changing cell format to currency ALT H AN.

Sparklines
- Paste Special - Transpose (ALT H V T)
- Doughnut diagrams
- Fitting a trendline
- Scatter diagrams
- New functions - correl, interecept, rsq, slope, trend and forecast
- Our first array function, LINEST, requiring use of CTRL+SHIFT+ENTER
- Data tables (ALT A W T)
- Autofit columns (ALT H O I)
- Conditional formatting (ALT H L)
- Merging cells (format cells - alignment - merge)
Click to expand...


I use the keyboard Ctrl= etc much more than I use the mouse . One of the youguns on my team uses the mouse to select and use function - what a faff on imo - that said maybe its a age thing (58) and/or how you learned Excel / 123
 


Henners61 said:
I use the keyboard Ctrl= etc much more than I use the mouse . One of the youguns on my team uses the mouse to select and use function - what a faff on imo - that said maybe its a age thing (58) and/or how you learned Excel / 123
Click to expand...

Naw, you're just doing it properly. People who use Photoshop and DAWs professionally tend to rely on keyboard shortcuts because it's the efficient way to do it.
 
Henners61 said:
I use the keyboard Ctrl= etc much more than I use the mouse . One of the youguns on my team uses the mouse to select and use function - what a faff on imo - that said maybe its a age thing (58) and/or how you learned Excel / 123
Click to expand...
Same as me with AutoCAD, I learnt it when it was use a puck and find the right icon on a massive tablet, or learn the keyboard shortcuts. When I see people using the menus to draw a line it infuriates me.
 
TartanMackem said:
So I have had a bit of a career change and I'm more involved with sales these days. As a result I am now being confronted with excel spreadsheets. Lots and lots of spreadsheets, and I'm expected to create my own. Being a millenial I assumed that I was "ok with Excel" but now working with people who are actually good with it, I realise that I basically haven't got a clue.

What's the best way to learn? Online presumably? Anyone got any recomendations?
Click to expand...
:eek: didn't realise people were still using Excel. You don't work for Sunderland Uni do you? :lol:
 
TartanMackem said:
So I have had a bit of a career change and I'm more involved with sales these days. As a result I am now being confronted with excel spreadsheets. Lots and lots of spreadsheets, and I'm expected to create my own. Being a millenial I assumed that I was "ok with Excel" but now working with people who are actually good with it, I realise that I basically haven't got a clue.

What's the best way to learn? Online presumably? Anyone got any recomendations?
Click to expand...
Got a load of training videos on USB stick which walk you through all the functions available and a couple of book PDF’s for pivot tables.

If you’ve got a file share facility drop me the link (PM me is interested for email address) and I’ll happily copy them up for you.
 
TheWanderer said:
Got a load of training videos on USB stick which walk you through all the functions available and a couple of book PDF’s for pivot tables.

If you’ve got a file share facility drop me the link (PM me is interested for email address) and I’ll happily copy them up for you.
Click to expand...

Cheers mate, that would be grand. Tried to pm you but your inbox is full.
 
Henners61 said:
I use the keyboard Ctrl= etc much more than I use the mouse . One of the youguns on my team uses the mouse to select and use function - what a faff on imo - that said maybe its a age thing (58) and/or how you learned Excel / 123
Click to expand...

I do some investment bank training and one of the key drivers is the ability to use the keyboard as they deem it far quicker. The class I have at the moment, at Uni, I am hearing less and less mouse clicks as the weeks go on. Check out The Excel Shortcut Key game !
jlaws said:
I do some investment bank training and one of the key drivers is the ability to use the keyboard as they deem it far quicker. The class I have at the moment, at Uni, I am hearing less and less mouse clicks as the weeks go on. Check out The Excel Shortcut Key game !
Click to expand...


If anyone fancies some Friday Excel I copy below the homework challenge that was set this week. I don't know how to add an Excel file attachment here but you can get anyones last 3.2k tweets at Vicinitas : Twitter Analytics Tool for Tracking Hashtags, Keywords, and Accounts (you need a twitter a/c to login). This is a good test of use of $ signs, text functions, embedding functions within one another, of countif, the time function and an array functions FREQUENCY. Some of these students hadn't touched Excel three weeks ago.

Qn - Keiran Maguire is a regular tweeter (@@KieranMaguire). I have 3200 of his most recent tweets and I would like to do some analysis on this:

1. Each tweet is time stamped. I would like you to use text functions to extract the time and put it in column BN. Then in column BO I want you to use the TIME function to put the time in a consistent format.
2. I would like to know which days Keiran tweets the most. In column BP I want you to extract the first three days of the week and then in column BR count how many tweets he makes on each day.
3. How long are Keiran's tweets? This is a neat little solution @ Excel formula: Count total words in a cell | Exceljet . Find the average and place it in A3202.
4. Column B has the text of the tweet. We can separate these into separate columns. There is a neat little solution @ Excel formula: Extract nth word from text string | Exceljet
5. Keiran has been very critical of Derby County owner, Mel Morris. How many times does he mention Morris in his tweets? There is a neat solution at Excel formula: Count specific words in a range | Exceljet
6. In Q1 you extracted the time. There is an array function called FREQUENCY that can determine how many values fall in certain areas. I have filled column BS with the times (called bins). Apply the FREQUENCY function in column BT when does Keiran tweet the most. See FREQUENCY in Excel ( Function, Formula, Examples) | How to Use?

I will ask random students to demo their findings before the session.
 
Last edited by a moderator:
Kevj said:
In addition to the good advice on here relating to where to look for tutorials and that the best way to learn is to use the program, there is also some ‘etiquette’ which comes with using it regularly that makes your life far easier long term.

One example would be to forward plan how a document is laid out and how it interacts with other documents. Repetitive formatting is an easy way of getting data to talk to other tabs/ files as a common cell reference is easily linked across many, whereas random formatting is a pain.
Click to expand...
Very much this. There’s not much better in life than a well structured spreadsheet.
 
rey mysterio said:
Probably find out exactly what you need to do with it then ask around on forums or on here

watch YouTube videos for hacks

learn basic VBA - once you get your head around it it’s pretty easy and people who can’t use it think you’re mint

If there’s one thing to learn though - NEVER use vlookups. Learn how to use index/match instead. Loads better and faster.
Click to expand...
Index match match is the daddy
 

Back
Top