REPT can be used to pad values to a certain length or to build a simple in-cell histogram. To pad numeric values with zeros, a custom number format may be a better option. Examples To repeat “x” five times, you can use the following formula: Inputs to REPT can be variable. In the example shown above, REPT is configured to repeat the values in column B using the count in column C....
Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
where “list” is the named range B6:B11. where “list” is a named range that corresponds to the range B6:B11. The IF function requires a logical test to return TRUE or FALSE. In this case, the COUNTIF function performs the logical test. If the value is found in list, COUNTIF returns a number directly to the IF function. This result could be any number… 1, 2, 3, etc. The IF function will evaluate any number as TRUE, causing IF to return “OK”....
where data is the named range C5:I13. The result is an array with nine sums, one for each row in the range, as seen in column K. Note: in older versions of Excel you can use the MMULT function, as explained below. With the BYROW function In Excel 365, the most straightforward way to generate subtotals for each row is with the BYROW function. The purpose of BYROW is to process data in a “by row” fashion....
B4 contains a date value for January 5, 2016. The YEAR function returns the number 2016 representing the year of the date. Note that you can use YEAR to extract the year from a day entered as text: However, using text for dates can cause unpredictable results on computers using different regional date settings. In general it’s better (and more flexible) to supply an address to a cell that already contains a valid date....
Let’s take a look. Let’s pick up where we left off and work with the drink size headings in our sample coffee menu. Holding down the Control key, we can select all of these headings at once and work with them at the same time. First, let’s left-align the headings and reduce the font size a bit. We can do all of this using the controls on the ribbon. To bold the headings, just click the bold button on the ribbon....
In this video we’ll look at three functions that allow you to easily change case of text in Excel: UPPER, LOWER, and PROPER. In this worksheet, we have two columns that contain names. Column B contains last names in uppercase text, and column C contains first names with the first letter capitalized. In column D, I’ll add a formula that capitalizes the first name using the UPPER function. The UPPER function takes just one argument: the text you want in upper case....
All charts in Excel have a chart area, which encloses all elements of the chart. The easiest way to select the chart area is to click just below the top edge of the chart. The Chart Elements menu on the Format tab of the ribbon, and the Format Task pane title will confirm the chart area is selected. Once the chart area is selected, you can make a variety of formatting changes....
Here we have a table that contains five test scores for a group of students and an average score in Column I. How can we rank these students from highest to lowest scores? Well, one option is to sort the students by average score in descending order. Next, you can enter a “1” for the rank of the first student, “2” for the rank of the second student, and then just double-click the fill handle to copy that down....
Let’s take a look. Let’s quickly build a pivot table that shows total sales and order count by product. As usual, products are listed in alphabetical order by default. To sort a pivot table by value, just select a value in the column and sort as you would any Excel Table. We can do the same thing with Orders. Let’s sort orders in descending order. As always, we can hover over the sort icon to see the currently applied sort options....
Let’s take a look. Here’s the VLOOKUP commission example we’ve looked at previously. Let me quickly run through the example again to recap. To look up the correct commission value in this table with VLOOKUP, we give VLOOKUP the value to look up and the table array to use. In this case, I won’t use a named range, but I will convert the table to an absolute reference so that I can copy the formula down....
Let’s take a look. By default, the Quick Access Toolbar sits above the ribbon at the upper left of your screen. It displays a small set of icons that represent common, useful commands that you’d like to keep handy. To customize the Quick Access Toolbar, click the small arrow to the right of the toolbar. In this menu, you’ll see the currently enabled icons appear with a checkmark. In this case we see Save, Undo, and Redo....
In the example shown, the formula in E5 is: Last relative position, not row on worksheet When building advanced formulas that create dynamic ranges, it’s often necessary to figure out the last location of data in a list. Depending on the data, this could be the last row with data, the last column with data, or the intersection of both. Note: we want the last relative position inside a given range, not the row number on the worksheet:...
where quarter (C4:F4) and data (C5:F16) are named ranges. With a lookup value of “Q3” in cell H4, the result is all values associated with Q3, which spill into the range H5:H16. Note: this problem can also be solved with INDEX and MATCH and FILTER, as described below. Although this example shows off the simplicity of the XLOOKUP function, it can also be solved with a straightforward INDEX and MATCH formula, as described below....
Note: the LARGE function will easily return nth values, but LARGE will return duplicates when they exist in the source data. In the example shown, the formula in E6 is: Where “rng” is the named range B5:B11 Once we have the largest value established, we create another formula that simply checks all values in the named range “rng” against the “last largest value”: Note: this is an array formula and must be entered with control + shift + enter....
These formulas use the structured references available in Excel Tables. The formula in E5 subtracts the mileage in the row above from mileage in the current row to calculate distance (miles driven) since the last gas stop: The SUM formula is used only to prevent errors on the first row, where the row above contains a text value. Without SUM, the first row formula will return the #VALUE error. The SUM function however will treat the text in C4 as zero and prevent the error....
Fields The pivot table shown is based on two fields: State and Color. The State field is configured as a row field, and the Color field is a value field, as seen below. In the Pivot Table, the Color field has been renamed “Colors”, and “Summarize values by” has been set to “Distinct count”: Data model When the Pivot Table is created, the “Add this data to the Data Model” box is checked....
In the example shown, the formula in B4 is: Which returns a random number from the numbers provided. In this case, we are providing four numbers as options: 25,50,75,100, so we need to give CHOOSE a number between 1 and 4. To generate this number, we use RANDBETWEEN, a function that returns a random integer based on a lower and upper bound. Since we are only working with 4 values in CHOOSE, we supply 1 for the bottom number and 4 for the top number....
