How to build a simple dynamic chart
In this video we'll look at how to build a simple dynamic chart in Excel. A dynamic chart automatically responds to changes in data...
Dave Bruns
Floating column chart with up down bars
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.
Count rows that contain specific values
In this example, the goal is to count the number of rows in the data that contain the value in cell G4, which is 19. The main challenge in this problem is that the value might appear in any column, and might appear more than once in the same row. If we wanted to simply count the total...Read more
Sum top n values with criteria
In this example, the goal is to sum the largest n values in a set of data after applying specific criteria. In the worksheet shown, we want to sum the three largest values, so n is equal to 3. At a high level, this problem breaks down into three...Read more
Indirect named range different sheet
The formula above evaluates something like this:
=SUM(INDIRECT("'"...Read moreMAXA Function
The MAXA function returns the largest numeric value in a range of values. Like the MAX function, MAXA ignores empty cells. However, unlike the MAX function, MAXA evaluates the logical values TRUE and FALSE as 1 and 0, and evaluates text as zero when...Read more
MINA Function
The MINA function returns the smallest numeric value in a range of values. Like the MIN function, MINA ignores empty cells. However, unlike the MIN function, MINA evaluates the logical values TRUE and FALSE as 1 and 0, and evaluates text as zero...Read more
XOR Function
The XOR function performs what is called "exclusive OR", in contrast to the "inclusive OR" performed by the OR function. Whereas the OR function returns true if any input is TRUE, XOR only returns TRUE in specific cases. In the simplest case,...Read more
Count times in a specific range
The COUNTIFS function takes one or more criteria, entered as range/criteria pairs. In this example, the first range/criteria pair is:
B5:B11,">="&E5
Matching any time greater than or equal...Read more
Highlight numbers that include symbols
The formula first uses the ISNUMBER function to test if the value is a number, and applies a simple logical if so:
=IF(ISNUMBER(B4)
For any number less than the value in "input", the formula will return TRUE and the conditional formatting...Read more