Conquer Excel
Quick, clean, and to the point. Join more than 100,000 people who get weekly tips from us.
Formulas
To sum by week, you can use a formula based on the SUMIFS function. In the example shown, the formula in H5 is: = SUMIFS ( total , color , $G5 , week , H$4 ) where "total" is the named range D5:D16, "color" is the named range B5:B16, and "week" is the named range E5:E16. How this formula works The...Read more
To find the first Monday before any date you can use a formula based on the WEEKDAY function. In the example shown, the formula in C6 is: = B5 - WEEKDAY ( B5 - 2 ) How this formula works If you imagine you have any random date and want to look back in time to find the nearest Monday, you can see...Read more
To get the grand total for a value field in a pivot table, you can use the GETPIVOTDATA function. In the example shown, the formula in I6 is: = GETPIVOTDATA ( "Sales" , $B$4 ) Although you can reference any cell in a pivot table with a normal reference (i.e. F11) the GETPIVOTDATA will...Read more
If you want to highlight rows in a table that contain specific text, you use conditional formatting with a formula that returns TRUE when the the text is found. The trick is to concatenate (glue together) the columns you want to search and to lock the column references so that only the rows can...Read more
If you want to sum only the visible rows in a filtered list (i.e. only those rows not filtered out), you can use the SUBTOTAL function with function number 9 or 109. What makes SUBTOTAL especially useful is that it automatically ignores rows that are hidden in a filtered list or table . Following...Read more
Videos
In this video, we look at how to build a chart that automatically includes new data, and dynamically highlights the top and bottom values. This is an easy way to create an interactive chart...
In this video, we look at how to use conditional formatting, with a formula, to shade every other row in a table. This is sometimes called "zebra striping", and it's a cool...
If you're building a pivot table from data in an Excel worksheet, you should be using a table for the source data. In this short video, we show you how to use a table as the data...
When you copy and paste a pivot table, a hidden data cache still remains. This video shows a simple way to copy a pivot table without the source data, while preserving all formatting.
In this video, we introduce the concepts of boolean values and boolean logic. Boolean logic can be a great way to simplify and speed up complex formulas, since it requires no branching.
Blog
Criteria are a key concept in Excel, but building useful criteria for text, numbers, dates, times, etc. is hard because it requires a good understanding of how Excel handles data. This guide will help you build formulas that work the first time with over 50 examples.
Every once in a while, you might find Excel behaving in a bizarre or unexpected way. One example is when you accidentally trigger the scroll lock feature. Another example is when one or more formulas suddenly stops working. Instead of a result, you see only a formula. What?
Excel Tables have a boring (and confusingly generic) name, but they are packed with useful features. This article is a summary of the things you should know about Excel Tables.
In this challenge, the goal is to end up with a text string like "MWF" for Monday, Wednesday, Friday. The problem is that the weekdays are input like as yes/no abbreviations like "NYNYNYN" for "MWF". What formula will translate the "N" and "Y" to weekday abbreviations?
Every day, thousands of questions about Excel are posted on the internet. Many of these questions go unanswered because they are unclear. This article contains some tips on how to write a question that people will quickly understand and answer if they can.
Feedback
I don't think you understand how much your training has changed my life in terms of work efficiency and effectiveness not to mention minimizing my stress, anxiety and enabling me to shine in front of employers. -Veronika
Thank you so much! I really like your website it has really helped me teach myself more excel tricks which has helped me with work! -Jaime
Very satisfied with the product! It has made me more efficient at work! -Evan
We offer free resources and paid training.