will introduce you to the exciting new pivot table features in Excel 2007. Create quick summaries and pivot charts, add impact with traffic light icons, design calculated fields, group dates and numbers.
14 Basic Skills For Excel Users
Last week, John Walkenbach posted a list of basic skills for men, then basic skills for women, and finally, basic skills for dogs. That got me thinking – what’s on the list of basic skills for Excel users? If you use Excel at work, or list it on your résumé, what tasks should you be able to do?
I’m sure your list is different, but here’s mine. Would any of these make your list?
- Sum or Count cells, based on criteria
- Build a Pivot Table
- Write a formula with absolute and relative references
- Create a drop down list of options in a cell
- Sort a list without messing up the data
- Use a formula to look up a value in a table
- Record and modify a simple macro
- Design and build a simple application, such as an order form
- Create a chart from worksheet data
- Apply conditional formatting that uses a formula
- Nest two or more functions in a formula
- Insert and protect worksheets (and understand the limits of Excel's protection)
- Perform simple date and time arithmetic
- Filter unique items from a list
______________________
July 13th, 2009 at 11:10 am
15. Convert a number or a date that Excel "thinks" is a string to a number you can calculate with.
July 13th, 2009 at 11:28 am
16. Check data for values which are stored as text not values, happens a lot when importing data from Access
July 13th, 2009 at 3:30 pm
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
July 13th, 2009 at 8:42 pm
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.
July 14th, 2009 at 7:14 am
Clayton, That would be like:
=if(PreviousPostNo = 16, PreviousPostNo + 1, PreviousPostNo + 1)
July 14th, 2009 at 1:35 pm
@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/
July 16th, 2009 at 5:38 am
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?
July 16th, 2009 at 5:01 pm
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")
July 21st, 2009 at 7:16 pm
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)
July 22nd, 2009 at 5:09 am
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
July 22nd, 2009 at 1:25 pm
Use Sumproduct() to do complex summaries without intermediate calculations.