Hi, I need to get back to work and I've got myself an interview for next week but I need to quickly brush up on my advanced Excel knowledge (its been a LONG time) anyone have or know of good free sources for excel stuff like pivot tables and functions?

thanks,

 

Just send you a personal message. Good luck!

 

Google Excel Magic Tricks. That guy is sick. His voice however, is another matter.

Anything you want to know ;). QTools to VLOOKUP, and wacko stuff if you want. Recording Macros, VBA/SQL to access backend Oracle databases. Or Infor! (ohayu!) MS PowerBI. PowerPivot, PowerQuery. Bar Coding for manufacturing. Production planning. Whatever dude.

Learn VLookup first and also how to setup Named Ranges in a table using keyboard shortcuts. It helps with Formula Writing. ALOT.
Vlookup is the most powerful function in Excel on massive databases. Conditional Formatting based on results of a Formula. Pivot tables are a 5 minute deal.

The guy at Chandoo.org is very cool. Check him out. Skip the books.

You can ask me whatever you want. I don't need to google any of it. Might as well install Solidworks while you are at it ;)

 

Last edited: Nov 7, 2022

@Demon and @clone thanks a lot and I'll be taking a look now, finger crossed it'll all come back to me, its been a while....

 

Search Amazon for Kindle titles, sort by price. There are often many free books on any given day.

 

https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

if you control+N to name the range you do not need to specify a2,blah. Remember that you can use 1 or 0 instead of "True/false". Makes formulas even faster to write.

Others rather basic things that make normal users be like are Sumif/Countif and Sumifs/Countifs .Then couple them with lookups. sick stuff.

If you want to learn complex stuff like Array Formulas or [M], shoot me a pm. Anytime you need help friend. What sort of job are you going to be doing. Internal Auditor, HelpDesk, IT Manager? Or something hard like Data [M]onkey?

 

Last edited: Nov 7, 2022

Leila Gharani on YouTube has a video for just about anything you need to learn. She's really good, and fun to watch. If you've watched her you know what I mean.

https://www.youtube.com/c/LeilaGharani

 

WAY better than Excel Magic Tricks. That guy sounds like Jim Gaffigan. Dramatic Crescendo speaking does not make Excel fun.

 

I recommend this website:
https://www.excel-easy.com/vba.html

 

wow thanks for the tips I'll go through them all after some sleep. I've been programming all day, it used to be my income but since Covid I cant live on it anymore soooo back to Information Analysis @clone it's step up from data monkey, if its like my previous job its mainly getting data, cleaning it up best you can - concatenation rings a bell, trimming down and cleaning up fields and lots of VLookup and the like, all of which rings a bell but I need to get my hands on it all again. The jobs massive data sets that hold a wealth of info so think of a question that might be answered by interrogating the data set and get me (or someone in the team) to boil it down to a simple side of A4 with pivot tables and graphs... haven't done it since the early 2000's so I need an intense refresh prior to the test and interview, I've got a week so fingers crossed.

I'll go through all the great looking info in the morning but I'm feeling the dreaded sea sick and week feeling of too much screen time and coding, oh @Crinklebumps I'v grabbed a free Excel mastery guide from Amazon and I'll try not to look at it till tomorrow, thats such a great tip about Amazon, thank you,

and thank you all again I'll dive into it tomorrow

 

Concatenation is how you create Barcodes in Excel from Multiple Cells. I just said it differently. Very easy stuff.

When you talk about cleaning up junk, remember that VLOOKUP descends vertically down the first left column of the range. So sort Numerically first always. It cannot deal with empty rows and should be pruned first.

Get Power Query, if you have the option: https://powerquery.microsoft.com/en-us/excel/

This is the book I normally recommend for it: https://www.amazon.com/Data-Monkey-Guide-Language-Excel/dp/1615470344

It makes all this stuff very mind-spinningly fast

Bill Jelen is The. Dude. when it comes to all this stuff: https://www.mrexcel.com

Is this for some form of Inventory Records for some type of Manufacturer/Factory type of business?
If you are handling concatenated bar codes; also scanners doing Issues and Completions? lol. Scanner Time (read: oh boy) . There are various ones you can deploy, Bluetooth are usually best. They are offline devices from your actual Production Systems and an Auditor will never complain about it. They will probably just ask you to go work for them. ;)

 

Last edited: Nov 7, 2022

ah ha, I was wrong, no bar code stuff I thought it was the joining two cells together instruction - left and right join, wow I'm rusty. I'm not sleeping I've decided to power through and try to wrap up what I need to do with the programming (web stuff) so I've got more time to "revise" and refresh my Excel knowledge and get those skills back on.
The job is for the Health Service dealing with patient data for doctors / managers and government and so on, the kind of thing I was doing last time was, for example looking at identifying outbreaks of say childhood disease and seeing if there's correlation to uptake of immunization distribution across age and area, thats not a great example but you get the idea. The part that information can play in assisting and informing health care in a geographic area is fascinating.

 

You aren't wrong. A Concatenated Barcode uses a font which pulls data from multiple cells and puts them into a format the font can make scannable. It's the same function.

ps. I <3 r2r.

 

Last edited: Nov 8, 2022