On Windows, Ctrl 2 also toggles bolding. 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.
In the example shown, the formula in F5 is: Note that the VAR function ignores the “NA” text in C8, while VARA includes this in the variance estimate as zero. Like VAR, VARA also evaluates logical values, and text representations of numbers when they are hardcoded directly into the function as arguments. Variation functions in Excel The table below summarizes the variation functions available in Excel. Notes VARA assumes arguments a sample of data, not entire population....
where data (C4:L6) and group (C5:L5) are named ranges. There are ten columns of data in the range C4:L6. The goal is to filter this horizontal data and extract only columns (records) where the group is “fox”. For convenience and readability, the worksheet contains three named ranges: data (C4:L6) and group (C5:L5), and age (C6:L6). The FILTER function can be used to extract data arranged vertically (in rows) or horizontally (in columns)....
where 4, 8, 12, and 24 represent hours in the future. The result is 4 dates with times based on the start date in column B that spill into columns D:G. How Excel handles times In Excel, dates are serial numbers and times are fractional parts of 1 day. This means the date and time values are just regular numbers and can be summed, added, and subtracted like other numbers....
where chars is the named range B5:B30 containing the letters A-Z. As the formula is copied down the column, it generates a new 6-character text string on each line. In this example, the goal is to generate a list of random 6-character codes. The randomness is handled by the RANDARRAY function, a new function in Excel 365. RANDARRAY returns 6 random numbers to INDEX, which then retrieves 6 random values from the named range chars....
If you want to highlight cells that contain duplicates in a set of data, you can use a simple formula that returns TRUE when a value appears more than once. For example, if you want to highlight duplicates in the range B4:G11, you can use this formula: Note: with conditional formatting, it’s important that the formula be entered relative to the “active cell” in the selection, which is assumed to be B4 in this case....
Let’s take a look. Here we have a worksheet with three tables: one for dates, one for times, and one for dates with times. Let’s look at the dates first. In Excel, all dates are numbers, and the first day in Excel’s date system is January 1, 1900. To illustrate, let’s use a formula to set column C equal to column B. Now let’s format column C with the General format so that we can see the numbers without date formatting....
Let me show you an example. Here we have a list of names. We want to pull out the first name from the full name. There’s an Excel function called LEFT that will extract text from the left, so let’s start there. Rather than explain how LEFT works, let’s just try it out to see what it does. Once I open the parentheses, Excel will tell us what we need to provide....
Thermometer chart in Excel could be a good way to represent data when you have the actual value and the target value. A few scenarios when where it can be used is when analyzing sales performance of regions or sales rep, or employee satisfaction ratings vs the target value. In this tutorial, I will show you the exact steps you need to follow to create a thermometer chart in Excel....
Let’s take a look. If you know the function name, the easiest way to enter a function is just to enter an equal sign and start typing. For example, if I want to get the average of these test scores with the AVERAGE function, I can start typing the name. Excel will show a drop-down list of possible functions below by matching the characters you’ve already typed. As you type additional characters, Excel will shorten the list....
Having outliers is quite common in all kinds of data, and it’s important to identify and treat these outliers to make sure that your analysis is correct and more meaningful. In this tutorial, I’ll show you how to find outliers in Excel, and some of the techniques that I have used in my work to handle these outliers. What are Outliers and Why is it Important to Find these? An outlier is a data point that is way beyond the other data points in the data set....
In Excel, a checkbox is an interactive tool that can be used to select or deselect an option. You must have seen it in many web form available online. You can use a checkbox in Excel to create interactive checklists, dynamic charts, and dashboards. This Excel tutorial covers the following topics: How to Get the Developer Tab in Excel Ribbon. How to Insert a Checkbox in Excel. Examples of Using Checkboxes in Excel....
In huge data sets, it is practically impossible (or highly inefficient) to do this manually. Thankfully, there is a way to select blank cells in Excel in one go. Select Blank Cells in Excel Here is how you can Select blank cells in Excel: Select Visible Cells in Excel How to Select Non-adjacent cells in Excel? How to Deselect Cells in Excel How to Move Rows and Columns in Excel....
In the example shown, the formula in C5 is: Note both cell references have absolute and relative elements, so they are referred to as “mixed references”. In $B5, the column is “locked” so that it won’t change, and in C$4, the row is locked. As the formula is copied, this is what the references look like for the first 5 rows and columns: Mixed references are a common feature in well-designed worksheets....
It makes it easy for a user to manage data if it is in a tabular format. Just by converting a data range into a table, you are able to use a variety of features that will make your work super easy (I cover these features in detail below in this article). Creating an Excel Table If you have the data in the tabular format in a worksheet, here are the steps to convert it into an Excel table...
In the example shown, we need a certain number of items, and the items come in specific bundle sizes. To calculate how many items are needed, taking into account the bundle size, the formula in D5 is: For example, if you need 6 items, and the bundle size is 2, you’ll need 3 bundles. If you need 3 items, and the bundle size is 5, you’ll need 1 bundle (and you’ll end up with 2 extra items)....
returns the text “=IF(B5>=70,“Pass”,“Fail”)”. Dealing with errors The FORMULATEXT function will return the #N/A error when a cell does not contain a formula. To trap this error and display nothing when a cell does not contain a formula, you can use the IFERROR function like this: Alternately, you can use ISFORMULA and IF like this: Checking for specific text To check a formula for a specific text, you can use the ISNUMBER and SEARCH functions....
Where code (B5:B15) and qty (C5:C15) are named ranges. The result is a case-sensitive sum of quantities for each code listed in column E. Unique values To generate the list of unique values in the range E5:E8, you would normally use the UNIQUE function . However, UNIQUE is not case-sensitive, so it will not work in this situation. This article provides a formula that can extract unique values from a range of cells, taking into account upper and lowercase characters....
Where data is an Excel Table in the range B5:C16. As the formula is copied down, it returns a sum for each state in column E. Note the formula is using a wildcard (*) and extra space in the criteria. See below for details and for a case-sensitive option. Note: this example pulls together a number of ideas, which makes it more advanced. If you find this example challenging, this formula is a simpler introduction to the same idea, without using text from another cell for criteria....
