If you use Excel at work, or list it on your résumé, what tasks should you be able to do? Are there basic skills for Excel users that office workers should be able to perform with little or no help?
Basic Skills for Excel Users
I’m sure your list is different, but here’s mine. Would any of these make your list? There are videos for some of these skills below, and Excel courses that you can take, to improve your skills quickly.
01) Sum or Count cells, based on one criterion or multiple criteria
02) Build a Pivot Table to summarize date
03) Write a formula with absolute and relative references
04) Create a drop down list of options in a cell, for easier data entry
05) Sort a list of text and/or numbers without messing up the data
06) Create a worksheet formula to look up a specific value in a table
07) Record and modify a simple Excel macro and use it to save time
08) Design and build a simple worksheet template, such as an order form
09) Create an Excel chart from worksheet data
10) Apply conditional formatting that uses a formula as its rule
11) Nest two or more functions in a formula
12) Insert and protect worksheets (and understand the limits of Excel’s worksheet protection)
13) Create formulas for simple date and time arithmetic
14) Filter unique items from a list
Basic Skills for Excel Users – 1
This video shows skill number 1 in my list – how to sum numbers based on multiple criteria. This example uses the SUMIFS function to get the total.
Basic Skills for Excel Users – 2
This video shows skill number 2 in my list – how to create a pivot table to summarize data in Excel.
Basic Skills for Excel Users – 4
This video shows skill number 4 in my list – how to create a drop down list in a worksheet cell, with data validation. This makes is easier for people to enter valid information in worksheets
Basic Skills for Excel Users – 6
This video shows skill number 6 in my list – look up a specific value in a table. This example uses the VLOOKUP function to return a price for the selected product.
Basic Skills for Excel Users – 14
This video shows one of the basic skills for Excel users that you should have. It’s skill number 14 in my list – how to filter unique items from a list.
More Excel Resources
Excel Topics
Find more Excel tutorials, for these popular topics, on the Contextures website
Pivot Tables — Use Excel Pivot Tables to quickly summarize data from a list or Excel table. See how to get started, then learn more about the power of pivot tables!
FormulasUse Excel formulas and functions to summarize data, return values from a lookup table, do mathematical calculations, and much more!
Data Validation — Use the Excel data validation feature to create drop down lists on a worksheet. Or, use data validation rules to control what can be entered into worksheet cells.
Macros — Use Excel macros to automate routine tasks, so you can save time while you work. Record and edit your own macros, or use code that you find here on my Contextures site.
______________________
15. Convert a number or a date that Excel “thinks” is a string to a number you can calculate with.
16. Check data for values which are stored as text not values, happens a lot when importing data from Access
16 – Use the IF function. I use this function all the time but I still suprised by the number of engineers I teach that cannot use it.
Clayton Lock
I’m going to sneak this one in at Zero: Know how to structure your data, graphs, formulas, code so that you can remember what the hell you were doing 2 months later, and so that others can comprehend too.
Clayton, That would be like:
=if(PreviousPostNo = 16, PreviousPostNo + 1, PreviousPostNo + 1)
@Hui: no, that would always be like =PreviousPostNo+1 😛
After reading this and all the 3 original posts by John, I had to write one myself. So… here is the 14 basic skills for people making charts http://chandoo.org/wp/2009/07/14/chart-making-skills/
13. Perform simple date and time arithmetic
Hi Debra,
Never really worked with time arithmetic before, so you got me thinking…
What is 52.37 hours? (In days, hours, minutes, and seconds.)
Sounded like simple time arithmetic, so this is what I came up with. Parse the days, minutes, hours and stuff into hundreds, then use a custom format to show the resulting mess in a user-friendly way.
Enter 52.37 in cell A1,
put this simple little formula in a cell
=((((INT(A1/24)*100)+INT(A1)-24*INT(A1/24))*100)+INT(MOD(A1,1)*60))*100+MOD(MOD(A1,1)*60,1)*60
then format as custom with the format code:
# “days” ## “hr” ## “min” #0 “sec”
Works for me. Like I said, never really used this time arithmetic before. Is there a better way?
Ed, I’ve never had to show time in that format, but I might opt for simpler format:
=INT(A1/24)&TEXT(A1/24-INT(A1/24),”:hh:mm:ss”)
Whoops,
Even simpler =A1/24
Then use the custom format dd:hh:mm:ss
or the more ornate dd “days” hh “hr” mm “min” ss “sec”
(and let’s hope this web-thing does not substitute smart quotes again)
I’ve nevcer liked Custon Formats for time
Excel is inconsistant with its use of m which can be
m – minutes
mm – minutes with leading zero
m – month
mm – month with leading zero
mmm – month name short
mmmm – month name
Use Sumproduct() to do complex summaries without intermediate calculations.
How about – insert a blimmen column! Im here to check if my computer retarded work mate should know that, and seeing it doesn’t make the list because only because its so basic im going to say. Yes.
I think an overlooked basic skill is designing the spreadsheet. Everyone opens up Excel and just starts clicking and typing…
Here are some things you need to consider when creating a spreadsheet.
http://excel-diva.blogspot.com/2010/08/spreadsheet-design-101.html