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