We have a simple list of 4-digit alphanumeric codes like A001, A002, A003, etc. What formula can we use to mark codes that are "out of sequence" with previous entries? This problem includes two separate challenges.
In this formula challenge, we have data showing cups of coffee sold at a small kiosk for a week. What formula will look up and sum total cups sold after 12:00 PM on Tuesday and Thursday? Relevant cells are shaded in green.
In this formula challenge, we have four tests (Test A, Test B, Test C, and Test D), all with the same 19 questions, but listed in a different order. What formula can be used to match questions and construct an answer key for all tests? A perfect use for Excel's powerful lookup formulas.
INDEX and MATCH is the most popular tool in Excel for performing more advanced lookups. This is because INDEX and MATCH are incredibly flexible – you can do horizontal and vertical lookups, 2-way lookups, left lookups, case-sensitive lookups, and even lookups based on multiple criteria. If you want to improve your Excel skills, INDEX and MATCH should be on your list. See below for many examples.
Number formats are a key feature in Excel. Their key benefit is that they change how numeric values look without actually changing any data. Excel ships with a huge number of different number formats, and you can easily define your own. This guide explains how custom number formats work in detail.
The challenge - calculate the difference from the current and last entry, even when previous entries are skipped. This is one of those problems that seems simple at first glance, but is tricky when you look closely.
One of the most useful features of data validation is the ability to create a dropdown list that let users select a value from a predefined list. But how can you make one dropdown dynamically respond to another? In other words, how can you make the values in a dropdown list depend on another value in the worksheet? Read on to see how to create dependent dropdown lists in Excel.
Data validation can help control what a user can enter into a cell. You can use data validation to make sure a value is a number, a date, or to present a dropdown menu with predefined choices to a user. This guide provides an overview of the data validation feature, with many examples.
Sure, you can use a complicated WEEKDAY formula to generate a list of weekends. But with WORKDAY.INTL, you can do the same thing with a WAY simpler formula. The trick is to use a special 7-digit "mask" to filter out all dates except weekends.
In this article, we explain how to use concatenation to display friendly and useful messages in your spreadsheets. The messages are dynamic and respond instantly to changes, so the effect is polished and professional.
The EOMONTH function is one of those little gems in Excel that can save you a lot of trouble. It's a simple function that does just one thing: given a date, it returns the last day of a month. You can use EOMONTH to build all kinds of useful Excel formulas.
The CONCAT and TEXTJOIN functions make it possible to join values together in a range of cells. Since a range is just an array, this opens the door to some interesting new formulas that loop through values.
Named ranges make formulas easier to read, faster to develop, and more portable. They're also useful for data validation, hyperlinks, and dynamic ranges. This article shows you how you can use named ranges to build better spreadsheets, and better formulas.
In this article, we show you how to replace a complicated IF statement with a clever and compact formula based on MIN or MAX. This is a great tip any time you need to choose the smaller or greater of two values inside a formula.
Although Excel ships with many conditional formatting "presets", these are limited. A more powerful way to apply conditional formatting is with formulas, because formulas allow you to apply rules that use more sophisticated logic. This article shows 10 examples, including how to highlight rows, column differences, missing values, and how to build Gantt charts and search boxes with conditional formatting.
Excel has the ability to use a custom template for all new workbooks. If you find yourself making the same changes to new workbooks (changing row height, font size, zoom, etc.) a default custom template can save you time and trouble.
Just wanted to pass by and thank you for the great help you offer on your page. I looked up three different issues I had with a sheet in the last hour, and the three of them were solved by reading your tutorials. Great work!
Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.