To get started, let’s first set up some dates, so we have a visual representation to refer to. In C5, I’ll add a start date. Then, I’ll add and copy a formula below that simply adds “1” to each date above. The result is that we get a list of 14 consecutive dates. Now let’s show the day of the week for each date. There are several ways we can do this....
One common scenario where you have to perform calculations with dates would be to find out how many years or months or dates have elapsed between two given dates. This could especially be useful if you want to calculate years of service of employees in your company (where you have their joining date and the end date). In this tutorial, I will show you some formula techniques that you can use to calculate the years of service in years (or years and months and days)....
You’ve probably come across data that looks something like this before. It’s usually created by someone who doesn’t know what data should look like and thinks that the data should live in a summarized format because that’s how they want to view it. In this example, the sales amounts are scattered over 4 different ranges when they should all be in one column with a descriptive column heading like Sales Amount....
Where μ (mu) is the mean and σ (sigma) is the standard deviation. Normal Distribution Probability Density Function in Excel It’s also referred to as a bell curve because this probability distribution function looks like a bell if we graph it. It’s a well known property of the normal distribution that 99.7% of the area under the normal probability density curve falls within 3 standard deviations from the mean. So to graph this function in Excel we’ll need a series of x values covering (μ-3σ,μ+3σ)....
Let’s take a look. Here we have an empty pivot table using the same source data we’ve looked at in previous videos. Let’s add Product as a Row Label, and Total Sales as a Value. Now let’s sort the pivot table by Total Sales in descending order. To illustrate how Value Filters work, let’s filter to show only shows products where Total Sales are greater than $10,000. This will eliminate all of the products below “White Chocolate”....
Let’s take a look. In this worksheet, on the left, I have a list of 20 names. On the right, I have a much larger list of over 1000 names. How can I quickly figure out which names in the smaller list also appear in the bigger list? Well, there are several ways that we can approach this with formulas in Excel. We could, for example, use the VLOOKUP function or the MATCH function to find exact matches....
You apply number formatting (especially dates) to a value A cell containing a amount of text in a cell is formatted as a number A cell formatted as a date or time contains a negative value To fix, try increasing the column width first. Drag the column marker to the right until you have doubled or even tripled the width. If the cell displays properly, adjust the width back down as needed, or apply a shorter number format....
My first instinct was to use the RANDBETWEEN function. I did a quick check with some random data in Excel, and it failed me – there were repetitions in the results. Here is what I got when I used the RANDBETWEEN function: So, I had to resort to the RAND function. It works well with a negligible probability of the numbers repeating (I tested it multiple times on more than 100,000 cells, and there were no repetitions....
Excel has a lot of ways to group items that are the same, but these rely on values being an exact match. Data might be considered the same even though they are not an exact match. Grouping these similar items can be a difficult and often times manual task. Excel does actually have a feature that can group these close but not quite the same values together! It’s just a bit hidden....
The more you work with Excel, the more you would find yourself adjusting the row height and column width. But in some cases, you may want to lock the cell height and width so that a user cannot make any changes to it. One scenario where this may be needed could be when you have a fixed template that you share with other people and you don’t want them to mess up the formatting by changing the cell height/width....
Let’s take a look. By default, Excel charts will automatically move and resize when cells underneath them change. We can see this behavior in action if we increase or decrease the width of any columns underneath the chart. The same is true of rows. If we increase or decrease row heights, the chart automatically expands and contracts. You can control this behavior by adjusting the chart’s position property. Click the Format Selection button on the Chart Tools Layout tab....
One thing that often troubles new Pivot Table users is when they find that a Pivot Table Fields list (some people call this Pivot Table Fields menu) goes missing. You can’t do much with a Pivot table if you do not see the Fields list, and it’s not very intuitive on how to get it back (at least for new Excel users) In this short tutorial, I will show you two simple ways you can use to get back the Pivot Table Fields list....
Let’s take a look. SUMIFS has three required arguments: sum_range, criteria_range1, and criteria1. After that you can enter additional range and criteria pairs to add additional conditions. In the first set of tables, we’re using the named range called “number” and a named range called “color.” I’ll enter the formulas in column H to handle the conditions in column F. To SUM all numbers equal to 15, I enter the range “number” for both the sum_range and criteria_range1 and I enter “15” for criteria1....
This is an array formula, and must be entered using Control + Shift + Enter (CSE). When given a range of cells (an array of cells) ISERROR function will return an array of TRUE/FALSE results. In the example, this resulting array looks like this: {FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE} Note that the 6th value (which corresponds to the 6th cell in the range) is TRUE, since cell B9 contains #N/A. The MATCH function is configured to match TRUE in exact match mode....
In the example shown, the formula in G7 is: Where “Sex” is a named range for C3:C15 and “Score” is the named range D3:D15. Note: this is an array formula and must be entered using Control + Shift + Enter. The problem in this case is that we don’t want LARGE to operate on every value in the range, just values that are either male or female (M or F)....
Pie charts work best to display data with a small number of categories (2-5). The Pie of Pie Chart provides a way to add additional categories to a pie chart without generating a pie chart too complex to read. When configuring a Pie of Pie chart, Excel provides a setting that moves the smallest n slices of the pie to another smaller pie, where n can be adjusted to suit the data....
where key is the named range C4:G4. Note: This is an array formula. In Excel 365, enter normally. In older versions of Excel, you must enter with control + shift + enter. In cell I7, we have this formula: working from the inside-out, this expression is evaluated first: The result is an array of TRUE FALSE values like this: TRUE values indicate a correct answer, FALSE values indicate an incorrect answer....
Even basic formulas require absolute and relative references, so one of the most useful formula shortcuts is the toggle for references. For example, to complete this multiplication table, we need to multiply the left column by the top row. This means we have to lock both the column and row references to make sure they don’t change. Use F4 on Windows and Command + T on a Mac to toggle through the 4 options for absolute and relative references....
In this worksheet, we have some basic data subtotaled by region and quarter. The shortcut for grouping rows or columns in Excel is Alt Shift right arrow in Windows and Command Shift K on a Mac. If you only have cells selected (not entire rows or columns) this shortcut will cause Excel to display the Group dialog box. There, you can tell Excel to group either Rows or Columns. You can speed things up by selecting entire rows or columns before you group....
Where data is an Excel Table in the range B5:E16. In this example, the goal is to sum total hours in cell H5 and calculate total hours per person in the range H8:H10. All data is in an Excel Table named data in the range B5:E16. The table is used for convenience only, and is not required to solve the problem. The main challenge in this example is to correctly display time as a duration instead of time of day....
