How to learn Intermediate to Advanced Excel in Two Weeks?

  • Creator
    Topic
  • #191003
    whopper
    Participant

    Hi all,

    I just found a job that requires intermediate/advanced Excel knowledge. I’m at a beginner to intermediate level right now. What is the best online class or Excel book I can get for learning really really fast?

    Thanks in advance!

    REG - 89, 04/29/13
    BEC - 81, 08/06/13
    FAR - 84, 12/19/13
    AUD - 82, 10/05/13

Viewing 15 replies - 1 through 15 (of 15 total)
  • Author
    Replies
  • #636314
    OnMyWay732
    Participant

    https://www.techonthenet.com/excel/formulas/

    AUD - July 2014 - 76
    REG - August 2014 - 82
    FAR - November 2014 - 78
    BEC - January 2015 - 81

    DONE!!!!

    Used Becker online. Who needs a text when you can burn your eyes out staring at the screen for months on end?

    "Let me tell you something you already know. The world ain't all sunshine and rainbows. It is a very mean and nasty place and it will beat you to your knees and keep you there permanently if you let it. You, me, or nobody is gonna hit as hard as life. But it ain't how hard you're hit; it's about how hard you can get hit, and keep moving forward. How much you can take, and keep moving forward. That's how winning is done!"

    #636315
    Anonymous
    Inactive

    What kinds of things you should learn probably depends on what kinds of tasks you will have to do with the position. From personal experience, the functions and formulas that can be considered “advanced” that I have learned to use after college are vlookups, pivot tables, and general keyboard shortcuts that have made projects go quicker. I would start by looking up those types of things (just a google search will give you plenty of good resources).

    #636316
    ScarletKnightCPA
    Participant

    Pretty much learn by doing and experience.

    Just tinker with the pivot tables and know that the formulas exist vlookups, if formulas and such. You can figure out how to implement most of the formulas through trial and error and googling it.

    Far: 76 (Wiley Test Bank)
    Aud: 77 (Wiley Test Bank)
    Reg: 61, 76 (Wiley book, Wiley Test Bank)
    Bec: 86 (Wiley Test Bank)

    MBA in progress

    #636317
    stoleway
    Participant

    Being advanced in excel will take you a long time, but it will take you less time if you're more specific about you actually need. You might not need every bits and pieces of excel. Find out the type of excel skills that is related to this job and work on it. Most popular skills are Vlookup, Hlookup, match and indexing, pivot tables, shortcuts and knowing a little bit of macros. You can advanced to VBA but what you need right now should be job specific.

    REG -63│ 84!!
    BEC- 59│70│ 71 │78!
    AUD- 75!
    FAR- 87!

    Mass-CPA

    #636318
    Mamabear
    Member

    I took an “advanced” Excel class a few years ago and they went over pivot tables and formulas, which I already knew and considered intermediate. Maybe your definition of intermediate is equal to that hiring manager's definition of advanced. I require decent excel skills for my employees and I discuss during the interview what formulas, etc. they will be required to know and will use often and that pivot tables are a must. Did the hiring manager not discuss the depth at which you need to know Excel? Or have you not interviewed yet?

    CPA Exam - Finally DONE (November 2014)
    BEC (08/10/13) 80
    AUD (08/24/13) 65 (11/13/13) 85
    FAR (04/12/14) 81
    REG (07/19/14) 69 (11/29/14) 87!!

    #636319
    rsrasc
    Member

    https://www.youtube.com/user/ExcelIsFun

    you may like it and learn something-please check it out

    #636320

    If you're familiar with vLookups, you might want to try the Index/Match combination formula…saves from having to have the data sorted just so for vLookup

    https://www.mbaexcel.com/excel/why-index-match-is-better-than-vlookup/

    A 88, B 76, F 84, R 76 Passed 2014

    Licensed in OK

    #636321
    ocboa
    Member

    Good suggestions

    #636322
    whopper
    Participant

    Thanks guys. This is for a cost accounting position. At the interview, I was asked on a scale from 1-10 (10 being highest) how good my Excel skills were and I said around a 6 or 7. The interviewer mentioned they used Excel heavily , she specifically mentioned pivot tables & vlookups. I have used those functions before but it's been awhile and I didn't feel it was very complex.

    I may have oversold myself a little lol (please save me the lectures haha!) but I'm confident I can get a good grasp before the job starts and then pick up as I'm trained on the job. Just wanted to get some feedback on some good learning tools out there. When I Googled, there was so many Excel trainings that came up, I figured it would be good to hear from some accounting professionals.

    Love the suggestions so far!

    REG - 89, 04/29/13
    BEC - 81, 08/06/13
    FAR - 84, 12/19/13
    AUD - 82, 10/05/13

    #636323
    Peterman25
    Participant

    SUMIF is a good one…

    Definitely 2nd the keyboard shortcuts. Those make the most mundane tasks in excel go sooo much faster. I had a new staff accountant show me a bunch over the last 6 months and it is just ridiculous what I was missing out on.

    So much emphasis on pivot tables. I don't have the need for them in my job so I am not 100% familiar with them. Are they really that difficult to learn? With my current excel knowledge I feel like I would get the basics on them in an hour. I'm not discounting the skill or the knowledge I just don't think that it would take that long to understand or get the hang of it.

    Plus, people should remember that if you are wanting to do something in excel and are having a hard time trying to figure it out Google is your friend. There is a good chance that what you are tying to accomplish isn't new and can be found in a search in a few minutes. I've cut hours off of old procedures in my job because of this.

    BEC 7/14 - PASS
    FAR 10/14 - PASS
    AUD 1/15 - PASS
    REG 4/15 - PASS

    AZ license - Official 8/20/2015

    #636324
    ScarletKnightCPA
    Participant

    I don't consider pivot tables and vlookups advanced personally but from ‘advanced' classes i've taken, apparently it is.

    Far: 76 (Wiley Test Bank)
    Aud: 77 (Wiley Test Bank)
    Reg: 61, 76 (Wiley book, Wiley Test Bank)
    Bec: 86 (Wiley Test Bank)

    MBA in progress

    #636325
    Anonymous
    Inactive

    what does everybody use vlookup for? I never needed it ever. I am mostly using pivot table which is very very easy and different variations of IF formula. Conditional formatting is one the most useful simplified features. I used to snobbishly look down on it but am using it all the time now.

    I have taken visual basic class so that I could write macros but that never really happened, I am still hoping to implement this excel feature one day.

    There are so many people who are so very bad with excel, especially older ones, so if you learn to use some simple formulas and pivot tables it will probably be considered advanced. Good luck

    #636326
    Anonymous
    Inactive

    Google + practice. Most of my excel knowledge (which I may consider intermediate) were learned from Google and experience. Aside from knowing the recommended formulas, you must also learn the basic keyboard shortcuts (Ctrl keys & F keys). Work is a lot quicker if you use both hands.

    If you're stranded in an island and have nothing but 4 logs, arrange them to form F1, for sure they will know it means HELP!

    #636327
    Mamabear
    Member

    Short cut keys for sure. My favorite (aside from the typical ctrl+C for copy and ctrl+V for paste) is shift+ctrl+arrow keys to select the data all the way to the end of the data set. If I could convince my boss to use that one it would save a lot of time from me having to stand behind her while we go over reports. Shortcuts make a huge difference in how long it takes to accomplish tasks. I printed the shortcut list from Excel help and I spent one entire day not using my mouse so I forced myself to learn the shortcuts. Things went slow that day, but it has been wonderful for my time management since then.

    I use vlookups almost daily. We have 250 sales centers and I can create almost any report from various SQL output files using vlookups. I pull in Region numbers, EBIT numbers, employee headcount, # of customers, etc, which you can't get all in one report from SQL. I also use vlookups to pull numbers into my JEs. For instance, I have an allocation JE where we have to charge the stores for their trash usage from an excel spreadsheet from the vendor. I replace the old spreadsheet with the new spreadsheet each month and the JE is automatically updated because of the vlookup formula. I don't have to copy/paste into the spreadsheet. Vlookups help eliminate errors from manual entry.

    I use pivot tables to summarize data before putting it into the JE. For instance, we receive a list of charges from the marketing department for their items (hats, shirts, pens, etc) that were given to customers at each sales center during the month. Use a pivot table to get one marketing charge per sales center. Then use a vlookup from the JE to the pivot table to pull in the charges. The more processes that can be automated like that, the less chance you have for manual errors.

    SUMIF is great as well. We use that on our final financials to make sure the Revenues, Costs, Gross Profit, SG&A, and EBIT for the sales centers equal our consolidated amount. It's a great check figure tool and it helps me figure out when new sales centers or administrative branches have been added or when an error has occurred at one branch.

    We have had a new financial analyst come in within the last year and he knows Power Pivots, Advanced Macros, etc. I am amazed whenever we work together at the things he can do with Excel and now that I am done with the CPA exam I am making it my priority to train with him and learn what he knows. Those things are what I would consider Advanced, not vlookups, pivot tables, and basic formulas, but it really depends on the Excel level of your superiors/coworkers as to what is actually considered Advanced versus Intermediate.

    CPA Exam - Finally DONE (November 2014)
    BEC (08/10/13) 80
    AUD (08/24/13) 65 (11/13/13) 85
    FAR (04/12/14) 81
    REG (07/19/14) 69 (11/29/14) 87!!

    #636328
    Anonymous
    Inactive

    I think the most important Excel-related skill is the ability to Google and implement what you find. In a way, I'd say it's a comfort with/lack of fear of Excel that enables this. I have awed coworkers with things that I didn't know 5 minutes prior, but after a quick Google search I was able to automate forever something that they'd previously spent hours per week or month updating. I learned pivot tables, vlookup, fancy conditional formatting, etc. on the fly like that and in an hour went from clueless about them to the office expert on them. I'm not saying this to brag – just to say that the only thing I had that my coworkers didn't was the confidence to Google and implement what I found. So, anyone can be an Excel expert if they can Google and use what they find.

    Things like VBA for macros can be a bit more complicated. I took 2 programming classes in college which didn't use VBA but taught the basics of programming, so I can fairly easily use Excel to record a macro and then edit it to make it do what I want, even though I can't program one start-to-finish. I'd advise anyone who is curious to learn some VBA basics to do it that way – record a macro with Excel, then use a different set of data with it (like use the November data to record, then put in the December data and run it again) and see what messes up, then edit the macro to fix it. For example, if you sorted 500 rows of data for November, then the macro is set to select and sort 500 rows. If December had 600 rows of data, then you'd end up with 100 un-sorted, so you'd go in to the macro, find the 500, and change it to a larger number so that future months would be included (maybe 1000 so that even in a large month, it'd cover everything).

    Playing around with “dummy data” and these various tools is the best way to learn them. There is familiarity gained through frequent use which makes this all work quicker, but it can be done without that familiarity, just be a bit slower.

    P. S. I used vlookup's all the time at my last job – they're so useful for any time that you're trying to pull out a piece of data from or to a large workbook. The most recent one that I remember was adding the customer name next to the customer code in a workbook with around 20,000 – 30,000 rows. In the first customer name cell, I set up a vlookup to reference a listing of customer names and codes and pull the matching name, then copied it down to all 20,000+ rows and voila, customer names were added. Quick copy and paste-as-values (an important thing to know exists!) for the column and I had customer names added with just a few minutes work. I've only been at my current job for a couple weeks, but once I learn the current processes more, I'm sure I'll utilize vlookup all the time here as well.

Viewing 15 replies - 1 through 15 (of 15 total)
  • You must be logged in to reply to this topic.