It’s a plus if you have ms office listed on your CV and that includes Excel, but since I haven’t used it at pretty much any point of my life, what should I learn? What would at least be considered basic tasks done there?
I can make 3d models and edit videos, so I can deal with more or less complicated programs, but I don’t even know what I’m supposed to know here, I just know that there are rows and columns that you can name and that you can do some math by combining them.
You should know how to import and export data, change cell formats, make simple line and scatter plots, and do simple arithmetic and concatenation operations. Anything more complicated is better done in a more functional or purpose-designed application, particularly anything requiring complex manipulation or data visualization of multivariate data. It makes my soul hurt to watch engineers trying (and mostly failing) to plot million element data records or perform complex transforms in Excel.
Stranger
It’s a hugely versatile program for data management/manipulation. Pretty much anything you can think of along those lines, you can make it do if you’re imaginative enough.
VLookup is handy to have in the bag. I use it for decoding medical bills. I enter a bill code into a cell, Vlookup looks down a column of numbers I have stored on a totally different worksheet, finds that number, and returns the information in the adjacent cell. The database just has the codes in column A, and the meaning in column B. By keeping the database as its own separate document, I can have a different Excel doc for each of my clients. VLookup is kind of bitchy, though, in that numbers stored as text will confuse it.
You should know how to sort a block of cells. Like I will have a row that has a date in column A, the medical provider name in column B, a treatment synopsis in C, Treatment cost in D, CPT Billing code in E, and ICD 10 diagnosis code in F. Sorting by column A lets me put things in chronological order. Sorting by B lets me isolate treatment provided by a certain physician, etc.
Freeze Panes is kind of slick. You can lock certain areas of your spreadsheet so they don’t change as you scroll through data on a page. In my example, I lock the top row so that I can see the heading title on my columns, while the data scrolls under it.
Pretty basic, but know how to do hyperlinks. I’ve used Excel to serve as a hub for all kinds of frequently-used forms. I use it in my household budget to call up websites I use to pay bills. And since I can never remember passwords, I insert a comment next to the hyperlink with my username & password, all the security questions, etc. that the site is likely to interrogate me with when it decides I’m a crook trying to pay some stranger’s bill.
It really depends on what you are,trying to accomplish and how much experience you have.
I often get folks to set up a checkbook as a first exercise. This teaches most of the basics as well as allowing you to get a feel for navigating around and allows You to expand into data manipulation and graphing.
You can tack on other skills as you need them. I have seen too many people get bogged down in the complexity and trying to learn it all at once.
Oh, and get yourself a rubber brick to beat on the computer, MS software can be quirky.
Conditional formatting is a lifesaver. Especially for locating duplicate values.
Aside from the expected numeric calculations, you’ll want to manipulate, combine, separate, fold, spindle, and mutilate text data.
Two late for an ETA, but there are 5 pages of search results here on the SDMB with Excel in the title, most of them wanting to know how to do something in Excel.
Link.
Don’t everyone click at once. Don’t want to overtax the hamster.
There are several functions in Excel that can be done in a basic table or competitor’s product (though not necessarily as easily). And, of course, Microsoft continues to build new features and improve the existing ones all the time.
One of the features that seems relatively unique to MS Excel is their pivot table function. [Actually, no; I believe Corel’s Quattro Pro also had a similar function decades ago. However, I haven’t paid attention and don’t know if they’re even around any more.]
But it’s best to learn the basics first, and the previous responders have given you plenty of suggestions. Therefore I’ll simply note that it’s useful (but not required) to understand spreadsheet concepts in order to step up to database concepts and that the 3D modeling you already do can be tied to database data. That stuff can get really cool!
–G!
Well I’m not asking how to do something, I was asking what I would need to do, it’s a huge difference.
I once foolishly called MS support to try to fix a formatting issue in Excel (the program “lost” the formatting for a single cell out of hundreds). The person on the phone told me there were several million errors that have been reported. This was circa 2002 so I am confident MS has fixed these since then (not).
The fix? Loaded the spreadsheet into Open source spreadsheet software, open it, then save back to Excel format. Presto, problem solved.
Like I said, get yourself a rubber brick.
I hate to sound like I hate ME or anything, but so far, in professional and personal life have found open source programs to be superior.
Calc can do pivot tables How to Create Pivot Tables in Calc of OpenOffice - LibreOffice
Getting back to the OP when I have tutored folks the first question I ask is “What do you need to do?” , the second is “Show me what you know”. If you are a beginner, start with a simple project like the one described above to keep track of your bills I use this almost every day. It teaches the basics, formatting, sorting, simple formulas, hyperlinks and graphing You can then use the data on your purchases to learn more complex operations, including pivot tables
Just start with what you know, try something, and build your skills as you need them.
Good luck and have fun.
Missed edit
That should read MS. The auto correct here reminds me of Word,
Ooh ooh - let me add some stuff. I teach a beginning Office class at my community college, and there have been some good things already mentioned but I have some more. Here’s what I cover in my class:
Basic arithmetic, of course, but also know some of the more common functions: =MIN, SUM, AVERAGE, MAX, etc.
Know how to copy and fill cells. Look at Excel’s Flash Fill - it can be pretty smart (sometimes).
If you’re copying formulas between cells, you MUST learn about absolute cell references ($A$1 for example). My example in the class is for a table of employees and their pay rates, if one cell shows a pay raise at 7%, how would apply that to all of the employees? I think it’s interesting that the $A$1 style (with dollar signs) is a holdover from VisiCalc!
Inigo mentioned VLOOKUP, which is really great to know. Think of tax rate tables, for example. I use them all the time in my grading by setting 0 to 60 as an F, 60 to 70 as D, etc.
Learn how to do text concatenation, either with a =CONCATENATE function or by using the & operator (my preferred method).
Know how to import a file of text data, split text into multiple columns, and sort by column
Learn the basics of charts: pie charts, line charts, bar/column charts, and scatter X-Y charts, and where they’re best used. Know how to format and modify the chart title, axes titles, backgrounds, etc.
We also work through an example that I use from a summer camp signup roster in Excel. It has columns for names, addresses, cities, states, and zip codes, along with their T-shirt sizes. From that we generate address labels (and more) using Mailings from Microsoft Word (shows some of the integration capabilities). Then just for fun, I show them a simple pivot table that counts the number of T-shirts for each size (with two clicks).
I don’t do a lot with formatting (it’s pretty obvious), but I cover conditional formatting as well as some of the printing options.
I think these are great places to start, especially setting up a checkbook (or any sort of budget). It allows you to really learn what you’re doing, and for a pretty useful function.
I usually work building Excel knowledge towards being able to do a pivot chart. A pivot isn’t necessarily entirely useful for everyone, but it does mean you’ve been able to enter in sufficient information in a roughly-correct format in order to accomplish. It also gives the user a glimpse into the higher-level functions of Excel, and how daunting and/or enticing that may be (or not).
VLOOKUP(), especially in conjuction with MATCH() INDEX() ROW() etc.
IF() AND() OR() etc.
I don’t use Pivot often but many people do.
Importing data from a text file, and using Text to Columns properly.
Array formulas. Maybe some VBA too.
F4 for changing the cell references. Also one trick I use that a lot of people don’t seem to know about: a large number of blank cells, like if you have every other line blank. Select the entire area, F5, Special, check blanks. Then right click one of the highlighted blanks and delete.
If you claim to know Excel and are interviewing with me, I’m going to ask about VLOOKUPs and pivot tables. Those are the two things I’m going to need you to be able to do - and at the point you can do those two things, I’m going to assume that creating a spreadsheet to doing some simple math functions is easy.
Assuming you’re asking “So I have this thing, what’s it good for?”
… things to do in Excel:
Food…Aisle
Beans…4
Bread…2
Candy…17
Sort on the aisle column, and there is your grocery list in the order which you can get them from the store.
Name + Address lists for mailing, Christmas cards, etc. Print on clear labels, write “to you, from us” on all the cards, and get that chore out of the way in no time!
Keep track of all the times you were done wrong. Like that time you got a Christmas card from your brother signed “to you, from us”.
Inventory everything in your house… pictures, furniture, jewelry, etc. Take pictures of it. Write estimated replacement value, purchase cost, description, serial #'s in respective columns. Put pictures in file if you wish. Upload to the cloud somewhere (including pictures).
You now have an inventory of your property in case of loss from flood or fire.
And I provided a source of real life examples of what people need to know how to do in Excel.
I do stuff like that with it as well. It’s especially useful for interactions on message boards. User Name, Real Name (if mentioned), position on a given topic, hobbies, mentioned family members, when they’ve crossed you, etc.
what.
The funny thing about Excel is that it’s used for basically everything you can think of, even though it’s not particularly good at any of them. At least, not when compared with purpose-built tools. In my last job, here is a short list of things I used excel for:
Status report template
Task “tracker”
Gannt chart
Kanban board
CRM tool (basically keeping track of sales leads)
Address book
Market analysis tool
Financial modeling
Data templates
BI reporting dashboard
Statistical analysis
Data cleaning / data mastering tool
Really, the question is “what do you want to use Excel for”?
Do learn to work with formulas, which constitute a very powerful feature of the application. You probably already know that you can dynamically populate totals for numeric or monetary values, but you can also set up subtotals for different categories of data. At one job where I was required to track and categorize incident tickets, Excel made this relatively easy.