Conquer Excel
Quick, clean, and to the point. Join more than 100,000 people who get weekly tips from us.
Formulas
If you need to round a number up, regardless of the value of the digits being rounded (i.e. you want to force any number to round up, regardless of value) you can use the ROUNDUP function with a specified number of digits. In the example, the formula in cell D7 is = ROUNDUP ( B7 , C7 ) This tells...Read more
To check two items with exclusive OR (one or the other, but not both), you can use the XOR function. In the example shown, E5 contains the following formula: = XOR ( C5 = "x" , D5 = "x" ) This formula returns TRUE when either coffee or tea have an "x". It returns FALSE if both...Read more
To calculate total work hours between two dates and times, you can use a formula based on the NETWORKDAYS function. In the example shown, E5 contains this formula: = ( NETWORKDAYS ( B5 , C5 ) - 1 ) * ( upper - lower ) + IF ( NETWORKDAYS ( C5 , C5 ), MEDIAN ( MOD ( C5 , 1 ), upper , lower ), upper...Read more
To lookup a value by matching across multiple columns, you can use an array formula based on the MMULT, TRANSPOSE, COLUMN, and INDEX. In the example shown, the formula in H4 is: { = INDEX ( groups , MATCH ( 1 , MMULT ( -- ( names = G4 ), TRANSPOSE ( COLUMN ( names ) ^ 0 )), 0 )) } where "names" is...Read more
To get the last relative position (i.e. last row, last column) for mixed data that may contain empty cells, you can use the MATCH function as described below. Note: this is an array formula and must be entered with Control+Shift+Enter. In the example shown, the formula in E5 is: { = MATCH ( 2 , 1...Read more
Videos
Excel has great support for shortcuts in tables. Shortcuts for inserting, selecting, and deleting rows and columns all work much better! Watch the video to see how.
In this video, we look at how to make conditional formatting variable, so that you can change how formatting is applied without editing a rule. This elegant technique helps you build killer...
In this video, we look how slicers filter data in a pivot table and how to easily add a slicer to a pivot table.
Calculating a running total in an Excel table is a little tricky, because it's not obvious how to build the formula with structured references. You can easily get the current row with...
In this video we show how to highlight approximate match lookups with conditional formatting and the LOOKUP function. Highlighting approximate matches is tricky, because you must replicate...
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
Just wanted to say that your site is really, really helpful. Great work and thank you! -Lloyd
Just a note to say your site is fantastic! I've learnt more on here in 3 hours than I have in nearly 3 decades of using Excel. Keep up the great work! -Mark
Thank you for your clear explanation on the sumifs function. It was exactly what I needed to simplify a massive spreadsheet where I need to summarize a range of amounts from over 70 sheets in a workbook. The previous person would manually update the formulas each year but now the "sumifs between dates" formula will automate this. Huge Thanks!! -Sarah
We offer free resources and paid training.