Transcript In this video, we’ll look at how to apply number formats to a chart. One of Excel’s key features is the ability to apply number formats to change the display of numeric data, without affecting the data itself. For example, these numbers are using the “General” number format. If I switch to “Currency”, or “Accounting” they appear differently, but the actual values are unaffected, as you can see in the formula bar....
Explanation In this example, the goal is to copy every nth value from column B, where n is a variable that can be changed as needed. In Excel, it’s difficult to create formulas that skip rows following a certain pattern, because the references in the formula will automatically change as the formula is copied in 1-step increments across cells. However, with a little work it’s possible to construct formula references that follow specific patterns....
Explanation In this example, the goal is to count the number of items sold and remaining, based on the data visible in columns B and C. The ID column holds unique ids, and the Sold column is used to record a sale. An “x” in the Sold column indicates the item has been sold. As is typical in Excel, there are several ways to solve this problem. The article below explains two approaches....
Explanation Data validation rules are triggered when a user adds or changes a cell value. Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined. The ISTEXT function returns TRUE when a value is text and FALSE if not. As a result, all text input will pass validation, but numbers and formulas will fail validation. Data Validation Guide | Data Validation Formulas | Dependent Dropdown Lists...
Explanation Working from the inside out, the MID function is used to generate an array from text entered in B5 with this snippet: MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1) explained in detail here . The result is an array like this: {"A";"A";"A";"-";"1";"1";"1"} which goes into MATCH as the lookup value. For the lookup array, we use the named range “allowed”, concatenated to an empty string (""): allowed&"" The concatenation converts any numbers to strings so that we are matching apples-to-apples....
About This Shortcut This shortcut will decrease the font size of current selection but one “step”. Steps correspond to the sizes shown in the font size menu. About This Shortcut Apply the General number format. The General number format is the default format in Excel. It will display numbers as numbers, and text as text. Excel offers many types of number formatting .
Explanation This formula relies on a specific behavior of INDEX — although it seems that INDEX returns the value at a particular location, it actually returns a reference to the location. In most formulas, you wouldn’t notice the difference – Excel simply evaluates the reference and returns the value. This formula uses this feature to construct a dynamic range based on worksheet input. Inside the sum function, the first reference is simply the first cell in the range that covers all possible cells:...
Explanation In this example, the goal to calculate the difference as a percentage between two values then check the result to see if its within a given target percentage. The values come from the Expected and Actual columns in the worksheet. The challenge is that the difference might be negative or positive, and we need to cater to both. Difference as percentage To calculate the difference as a percentage, we can use a general formula like this:...
Excel 101 Core Excel Core Pivot Core Formula Dynamic Array Formulas Core Charts Excel Shortcuts Excel Tables Conditional Formatting All pricing is in US Dollars. We offer bundles and additional logins at a low cost. We also provide special pricing for students, seniors, companies, nonprofits, and groups. Contact us for a free quote. Use Excel for work? Persuade your boss . Learn at your own pace — all courses include lifetime access....
Explanation The FILTER function is designed to extract data that matches one or more criteria. In this case, we want to apply criteria that requires all three columns in the source data (Name, Group, and Room) to have data. In other words, if a row is missing any of these values, we want to exclude that row from output. To do this, we use three boolean expressions operating on arrays. The first expression tests for blank names:...
Explanation Although FILTER is more commonly used to filter rows, you can also filter columns, the trick is to supply an array with the same number of columns as the source data. In this example, we construct the array we need with boolean logic , also called Boolean algebra. In Boolean algebra, multiplication corresponds to AND logic, and addition corresponds to OR logic . In the example shown, we are using Boolean algebra with OR logic (addition) to target only the columns A, C, and E like this:...
Explanation In this problem, the goal is to find the first (earliest) time in and the last (latest) time out for a given name. This is essentially a lookup problem and the solution shown in the worksheet is an example of how you can sometimes use minimum and maximum functions to perform lookups. This works because Excel times and Excel dates are numeric values. Excel Table For convenience, all data is in an Excel Table named data in the range B5:D16....
One of the charts you’ll see around is a so called “floating column chart”, where columns rise up off the horizontal axis to depict some sort of value range. There are many ways to make this kind of chart in Excel, and Jon Peltier has a very comprehensive run-down here . This page describes just one approach, where you make a line chart with two data series (one high, one low) and then use “up/down bars” to create the floating columns....
Explanation In this example, the goal is to return an array with 7 subtotals, one for each day of the week, as seen in columns C:I. The numbers to sum are contained in data which is the named range C5:I13. This is an example of a problem where the goal is to create an array of sums rather than a single sum. We can’t use a function like SUM by itself, because SUM will aggregate results and return a single value....
Explanation In this example, the goal is to return a list of the locations for a specific value in a 2D array of values (i.e., a table). The target value is entered in cell N5, and the table being tested is in the range C4:L16. The coordinates are supplied from row 4 and column B, as seen in the worksheet. In the current version of Excel, which supports dynamic array formulas, an easy way to solve this problem is with the IF function together with the TOCOL function....
Explanation The MOD function performs the modulo operation. It takes a number and a divisor and returns the remainder after division. In this formula, we subtract one MOD result from another. For the first MOD, we use the number with a divisor that equals the place value we want times 10. For the second MOD, we use the number with a divisor equals to the place value we seek. The formula is solved like this:...
Explanation In this example, the goal is to highlight dates that occur on weekends. In other words, we want to highlight dates that land on either Saturday or Sunday. This problem can be easily solved by applying conditional formatting with a formula based on the WEEKDAY function together with the OR function. WEEKDAY function The WEEKDAY function takes a date and returns a number between 1-7 representing the day of week....
Transcript In the real world, you often need to concatenate values in a way that includes line breaks and other punctuation. In this video we’ll look at a clever way to make this task easier and less error-prone. A common example of a situation that requires concatenation is assembling a mailing address from data in separate columns. If I want to create a mailing address using this data, I need to create a formula that uses CONCATENATION to bring the name, street, city, state and zip together....
Transcript Using conditional formatting, It’s easy to highlight cells that match a certain condition. However, it’s a little trickier to highlight entire rows in a list that contains multiple columns. In this video, we’ll show you how to use a formula with conditional formatting to highlight an entire row in a multi-column list. Let’s take a look. Here we have a task list that contains a number of columns - priority, task, owner, and due date....
Transcript In this video, we’ll look at how to reverse the order of a chart axis. Here we have data for the top 10 islands in the Caribbean by population. Let me insert a standard column chart and let’s look at how Excel plots the data. When Excel plots data in a column chart, the labels run from left to right to left. In this case, the first column is Cuba, and the last is Barbados, so the columns match the order of the source data moving top to bottom....
