To make a more flexible, interactive conditional formatting rule, use other cells like variables in the formula. You must use an absolute address for E2 and G2 to prevent these addresses from changing. AND returns TRUEonly if all the conditions are met. If any conditions are not met, the AND function returns FALSE. When given TRUE, NOT returns FALSE. When given FALSE, NOT returns TRUE. Use the NOT function to reverse a logical value....
A histogram is a visual representation of the distribution of numerical data. The height of each bar shows the proportion of values in that bin. Typically, there are no gaps between bins to represent continuous data. In Excel 2016+, a histogram is built-in chart throw in.
Because FREQUENCY is a formula, the results and chart will dynamically update if data changes. FREQUENCY is a bit tricky to use, because must be entered as anarray formula. The range F5:F8 is the named range “bins”. FREQUENCY will treat each bin value as theupperlimit for that bin. In other words, each bin will include a count of scores up to and including the bin value. FREQUENCY will also return an “overflow count” the count of values greater than the last bin....
The lookup values must appear in thefirstrow of the table, moving horizontally to the right. HLOOKUP supports approximate and exact matching, andwildcards(* ?) for finding partial matches. HLOOKUP searches for a value in thefirst rowof a table. When it finds a match, it retrieves a value at that column from the row given. Use HLOOKUP when lookup values are located in the first row of a table. Use VLOOKUP when lookup values are located in the first column of a table....
For example, with a time of 9:00 AM, HOUR will return 9. HOUR takes just oneargument,serial_number, which must be a validExcel dateor a validExcel time. Times can be supplied to the HOUR function as text (e.g. “7:45 PM”) or as decimal numbers (e.g. 0.5, which equals 12:00 PM). The HOUR function will “reset” to 0 every 24 hours (like a clock). To work with hour values larger than 24, use a formula toconvert time to decimal hours....
This is a little strange and maybe even confusing. In many cases, this is just fine. The axis shows an accurate distribution of the data. However, there may be times where you want a simple, even distribution. In that case, you could switch the punch in to text. Now the data points are evenly spaced, and line up with the dates shown on the horizontal axis. Notice that even though the axis pop in is now text, Excel still understands the dates....
In Excel, both dates and times are just numbers. Let’s take a look. Let’s look at the dates first. To illustrate, let’s use a formula to set column C equal to column B. Now let’s format column C with theGeneral formatso that we can see the numbers without date formatting. And January 1, 2002 is equal to the number 37,257. That is, January 1, 2002 is 37,257 days after January 1, 1900....
One of the most useful features ofExcel Tablesis that they create a dynamic range. To illustrate, I’ll add someformulasto this worksheet to report on the size of this table. The first formula counts rows with theROWS function. The second formula counts columns with theCOLUMNS function. Both formulas use only the table name as a reference. I’ll also add another formula to calculate a count of all cells in the table....
Lets take a look. We also have access to Number Format parameters. Well cover these options in more detail in upcoming videos. you might right-click a value or item in thePivot tableand choose Field configs. No matter which method you use, youll arrive at the same place.
Lets take a look. This pivot table shows sales data by Product. If we check the source data, we see that we have columns for Quantity and Total sales. However, the data does not contain a unit price. To add a calculated field to a pivot table, first, select any cell in the pivot table. Then, on the Options tab of the PivotTable Tools ribbon, click Fields, Items & Sets....
A calculated item is an alternative to adding new rows to the data source. Lets take a look. Here we have a pivot table that is summarizing sales data by region. One option is to modify the source data to replace all instances of East and South with Eastern. However, we can achieve the same result using a calculated item. To create a calculated item, first select an item in the row or column field youre working with....
Let’s take a look. Here we have same worksheet we looked at previously. That way, we can adjust the conditions for the rules using the key itself. The first step is to create the basic layout for the key. For this, we’ll set up a small table with three rows - one for each conditional format. We can then add labels for each conditional format rule. These could be anything, but let’s use Excellent, Concern, and Danger....
Lets take a look. Lets start off by adding Product as a Row Label. Then lets add Total Sales as a Value. As usual, we get the sum of Total Sales. We can even add the same field again, and set the math function to Average. Lets remove the second and third instances of Total Sales and build a percent of Total View. Now we have Total Sales displayed as both a sum and a percentage....
