fbpx
Search
Close this search box.

Introducing Spreadcheats – Become Supergood with Excel in 30 days

Share

Facebook
Twitter
LinkedIn

spreadcheats-excel-spreadsheet-productivity-tipsStarting 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. 🙂

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

11 Responses to “Introducing Spreadcheats – Become Supergood with Excel in 30 days”

  1. AlexJ says:

    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

  2. Robert says:

    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.)

  3. CraigM says:

    Chandoo,
    Date sorting intricacies - by week, month, quarter, year, etc independent of format

  4. Ketan says:

    Dear,

    Following topics are very useful :
    Solver
    Goal Seek
    Scenario
    List
    Query writing
    ABC of Visual Basic(for layman)

  5. Denise says:

    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

  6. Cheryl says:

    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.

  7. nixnut says:

    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

  8. BDA says:

    Hey,

    Sounds interesting and am waiting for the updates 🙂

  9. chandoo says:

    @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. 🙂

  10. Bruno@Brazil says:

    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.

  11. Robert says:

    Bruno@Brazil,

    ozgrid provides several methods that might help you:

    http://www.ozgrid.com/Excel/ExcelProblems.htm

Leave a Reply