What book will help me develop mad excel skillz?

December 6, 2007 8:36 AM

What are the most important excel skills to have, and what book should I get that has them all?

Everybody unanimously agrees that the ability to work with excel spreadsheets and databases is one of the most important you can have. I have to agree with this. My only-slightly-more-than-basic knowledge of excel has already helped me to get some work, but I want to know more.

I can filter, do mailmerge, etc. but I'd like to know some more advanced stuff. What are some of the most important excel skills to have (everyone says pivot tables, which I can't do), and what books should I buy that will teach me them?

posted by Autarky to computers & internet (14 comments total)

Speaking as a non-programmer, but furtive user of Excel, the thing you want to get to know is Visual Basic for Applications(VBA).

With that, you can basically do anything you'd want to do with a computer, all running out of excel. Right now i've got a programme trawling through a couple of thousand excel documents in a variety of folders, copying and pasting out the information that I need, instead of me having to do that by hand. I have to say that watching it do that on the screen is quite literaly making me feel like THE MAN.

I would say the best way to start with that is to simply get a VBA for dummies book, and start by recording macros and then looking at the code in the visual basic editor. macros by themselves are insanely useful, but if you have a look at the code and gradually figure out how to alter it, then you can really say that you've PWNed excel.
posted by galactain at 8:50 AM on December 6, 2007

The mammoth excel thread from the blue in case you haven't seen it.
posted by ClanvidHorse at 8:53 AM on December 6, 2007

Vlookup!

As someone who LIVES in excel all day the vlookup formula has save me so much aggrivation. I agree that Pivot tables are also very important to know how to create. Along with knowing HOW to create a pivot table I think it is important to know WHEN to use a pivot table and when not to use a pivot table.

Too many times in my work I see people who do not understand pivot tables doing some pretty dumb things and then expect me to make it all better.

IMO you are not truly an EXCEL master until you can run excel without touching a mouse. Learning the proper keyboard shortcuts in Excel has sped up my work significantly.

I am not familiar with any good excel books. My advice is (if you can) to go to a bookstore with a large computer section , sit down and start reading through some of the books to see if you like the layout and the style of instruction. Then purchase that book and read it and practice what you are reading. Good Luck!
posted by remthewanderer at 8:58 AM on December 6, 2007

I know this is the wrong attitude entirely but...

When I was younger, I thought it would be cool to learn how to chant torah trope. After I did, lo and behold I discovered that this skill was so much in demand that I was asked to read torah practically every week at synagogue. From this experience I learned that sometimes it's better not to know how to do something that you're only moderately interested in, lest you be called on to do it again and again.

I've always treated Excel like this. I'm sure it would be good to know it, but I'm terribly afraid if I learn, I'll be asked to create spreadsheets for my job, over and over again.
posted by rouftop at 9:02 AM on December 6, 2007 [4 favorites]

rouftop, that's why I never learned how to use the new copier at my current job.

That said, I'm in a fairly senior job, not in accounting/finance or IT, and I use Excel all the time, even if it's just to check the work of the people who work for me. Not knowing how to quickly get the answer/create the summary pie chart hurt me one too many times to continue to blow off Excel (and the basic algebra/thinking that it takes to get it right.)
posted by pomegranate at 9:16 AM on December 6, 2007

Please, for the love of all that is holy, stop saying "Excel databases"!!!! Excel manages workbooks. Access manages databases.

One of the courses I teach is an introductory 4-hr Microsoft Office course, required for all degree-seeking students.

To pass my course, you have to know how to do the following (in addition to the very basics, like using the SUM function, and the sorting and mail merging you've already mentioned):

* create and modify charts and graphs
* work with formulas of all kinds, in the formula bar and the formula wizard
* work with absolute cell references
* work with multiple worksheets & references
* perform problem-solving analysis ("what-if")
* conditional formatting
* import and export data from and to Excel

The textbook we use is pretty good (although it also includes the other Office apps):

GO! with Microsoft Office 2007
posted by SuperSquirrel at 10:22 AM on December 6, 2007 [3 favorites]

I too hate the "Excel database" formation (and it is the subject of many inside jokes around the office) but I do have to grudgingly admit that the dictionary definition for 'database' is:
a usually large collection of data organized especially for rapid search and retrieval
which includes mosts Excel files.
posted by mmascolino at 10:34 AM on December 6, 2007

One useful Excel function which I think is often overlooked is using formulas to generate simple scripts or lines of code. For instance, frequently, lists of database updates come in as Excel documents. Rather than importing temporary tables, etc. it can save a lot of time to just use Excel to generate SQL update or insert statements.

Array formulas are really useful if you’re not already using them. I agree that VLOOKUP is also key. It’s also useful to learn how to use nested IF statements to test for multiple conditions, or to avoid displaying error messages... ie, IF(ISERROR(VLOOKUP({lookup statement goes here})),"Not found","Found")

If you need to have others enter data into your spreadsheets, data validation as an in-cell dropdown list is will save you a lot of headaches.

VBA macros can allow you to automate tasks that can otherwise take hours, such as fixing poorly formatted address data, removing hyperlinks, etc. David McRitchie’s site (http://www.mvps.org/dmcritchie/excel/excel.htm) has a ton of resources geared towards this type of thing.

There is also an entire category of functions in Excel that are pretty basic, but are not commonly known to people who don’t use Excel very often, mainly because the interface does not make it easy to know that they are there. Some of the features that people tend to ask me to show them again and again are:

Formatting a worksheet so that it prints on a single page
“Automatic” features like autofilter, autosum, etc.
Using Paste Special to change data type or transpose columns and rows
Importing a csv or other formatted text file
Working with simple formulas

Even though these are not exactly power functions, if you work in an office like mine, being able to show others how to do these things will get you a reputation for being good with Excel.
posted by OrlandoFurioso at 11:02 AM on December 6, 2007

Learn how to use Solver to your advantage.
posted by fourstar at 11:40 AM on December 6, 2007

Autarky,

Please mark SuperSquirrel's as the best answer, and then follow her advice.
posted by robhuddles at 1:55 PM on December 6, 2007

Second VLOOKUP (and, as important, IF() ), but really just get the hang of finding out how functions work. Stuff like SUMIF() and COUNTIF() are really handy in the real world.

Once you've got the hang of that, start looking into VBA.


Having said all that: the MOST IMPORTANT excel skill in a real-world office setting is teaching others to use vlookup, and being able to say "no, you work it out", because it isn't hard to become the go-to guy in a really annoying way...
posted by pompomtom at 3:31 PM on December 6, 2007

Make spreadsheets that your boss can't screw up when he/she starts messing around adding rows and columns. (They can just never resist.) Know absolute/relative addresses inside out and how copy/paste/drag/insert deals with them. Learn how to set protection on cells. It'll buy you the time you need to learn the more advanced stuff.
posted by klarck at 6:01 PM on December 6, 2007

Analyzing Business Data with Excel By Gerald Knight, at Oreilly, is awsome. It starts where other "advanced" Excel books stops.

The most powerful functions and tricks of Excel are:

- DSUM and all the database functions
- OFFSET, when combined with LINE and COLUMN
- The above when combined with DSUM
- Array formulas, especially when combined with the above three
- The solver
posted by gmarceau at 8:50 PM on December 6, 2007

I think you're smart to want to ask this question. Excel is not a focus or required skill for my job, but I have found that it enables me to perform analysis and create solid documentation far more quickly than many of my peers. Unfortunately, it's a quirky program with years and years of code behind it, so it is hard to find a reference that is helpful for all situations.

Here are some features that have been insanely helpful to me, they might be good basics for you as well:
* Formulas (VLOOKUP, IF, AVERAGE, SUM)
* Pivot Tables
* Using Names for cells - this allows you to pull a cell value from one worksheet to another without memorizing the cell location. It works for lists of items as well.
* If you have a list of values (i.e. A A B C B), and you want to filter the list to only show the unique items (A B C), select the column (including the column header), select Data > Filter > Advanced, select "Copy to another location", choose a blank column, and select "Unique values only".

Some indispensable keyboard shortcuts:
- ALT + O + E : Format Cell dialogue
- ALT + I + R : Insert Row (above your selection)
- ALT + I + C: Insert Column (to the left of your selection)
- ALT + E + D + down arrow : Delete row
- ALT + D + S : Opens sort options for selection
- ALT + D + F + F : Auto-filters selection (you need to be selecting the header row)
- ALT + D + F + A : Opens advance filter dialogue

One other thing: if you're using Excel on the Mac, don't bother. It doesn't have half the features of Excel on the PC. Invest in a copy of Parallels and run it in emulation.
posted by designmartini at 10:51 AM on December 7, 2007