Starting next week, PHD is going to feature a 30 post series on spreadcheats. The purpose of these posts is to make day to day spreadsheeting a breeze. Each post will be less than 200 words in size and aspires to make your day at office a little bit more productive.
I have already identified around 25 simple topics for discussing like: relative vs. absolute reference in functions, chart formatting, printing and sharing workbooks etc. But please feel free to drop your suggestions on what you would like to know. I like learning and sharing new stuff and your requests can motivate me to do that.
All the posts in this series will be tagged with “spreadcheats“. Once the series has a meaningful number of posts, I will create a seperate page where you can get all the links and discussion in one place.
Watch out for the first installment on next week.
11 Responses to “Introducing Spreadcheats – Become Supergood with Excel in 30 days”
Chandoo,
Topics that come to mind include:
Conditional Formating
Data Validation
Use of the OFFSET function
How to make a valid data list
Basic pivot tables
Chandoo,
I think this will be quite a challenge: "supergood in Excel in 30 days". I am looking forward to the series.
A few more ideas:
- selection of the most powerful functions: OFFSET (as Alex already said) the LOOKUPs, MATCH, INDEX, SUMPRODUCT, SUMIF, COUNTIF, SMALL, LARGE, INDIRECT, etc.
- Array formulas
- Autofilter, special filter and SUBTOTAL
- Custom number formats
- Use of form controls
- Keyboard shortcuts
- Customizing the toolbar(s)
- Trendlines for charts
- Solver
- Error handling in formulas
- Range names
- the must-have add-ins (Power Utility Pak, Name Manager, etc.)
Chandoo,
Date sorting intricacies - by week, month, quarter, year, etc independent of format
Dear,
Following topics are very useful :
Solver
Goal Seek
Scenario
List
Query writing
ABC of Visual Basic(for layman)
Mr PHD - I also struggle big time with OFFSET - I know that it is a mighty fine function - If only I could get to grips with it!
Another function - INDEX. Can you try to cover this as well please.
Many thanks Mr Chandoo
Denise
I am really looking forward to this! I also would like to see some fundamental Visual Basic info. Are you going to be doing this in '07 or '03?
BTW, I just learned a new keyboard shortcut and I can't believe it took me this long to find it -- shift F11 for a new sheet.
Are you aware that juiceanalytics also did something in this area some time ago? See http://www.juiceanalytics.com/writing/excel-training-worksheet/
regards,
nixnut
Hey,
Sounds interesting and am waiting for the updates
@All: thanks so much for the suggestions. It is nice to know that you are eager to make me learn and share all these ideas.
Few clarifications:
- all of these would be based on excel 2003 (dont worry, most of them may work with 2007)
- the intention is to share simple answers to day to day excel problems that I have learned the hard way
- SInce the posts are going to be bite sized (200 words max) I wont be able to learn or share some of the more complex excel spreadcheat suggestions you have given like : array formulas, form controls or solver etc. But let me understand some of these things in depth and write a bigger post whenever I can.
Thanks once again. Let us use this thread to post any suggestions you may have.
Chandoo, nice idea you had!
I'm interested in these topics that had been already suggested. But I'd like also to suggest you to comment something about tips for saving a worksheet in smaller files. Several times we need to send a worksheet by e-mail, but my worksheets get only bigger and bigger. Internet gets faster every day, but some people complain if they have to open a 10 Mb attachment... I don't know if it's related with the way I use formulas, I'd like to hear something about it from you.
Bruno@Brazil,
ozgrid provides several methods that might help you:
http://www.ozgrid.com/Excel/ExcelProblems.htm