But if you can’t see any tabs and are wondering where has it disappeared, worry not. There are some possible reasons that may have been the cause of missing tabs in your Excel workbook. In this article, I will show you a couple of methods you can use to restore the missing tabs in your Excel Workbook. If you can’t see any of the tab names, it is most likely because of a setting that needs to be changed....
Variation functions in Excel The table below summarizes the variation functions available in Excel. Notes VAR.P assumes data represents the entire population. If data represents a sample, compute variance with VAR.S. VAR.P only evaluates numbers in references, ignoring empty cells, text, and logical values like TRUE or FALSE. Arguments can either be numbers or names, arrays, or references that contain numbers. Arguments can be hard-coded values instead of references. To evaluate logical values and/or text, use the VARPA function....
Example In the example shown, the formula in F5 is: Note that the VARP function ignores the “NA” text in C8, while VARPA includes this in the variance estimate as zero. Variation functions in Excel The table below summarizes the variation functions available in Excel. Notes VARPA assumes data represents the entire population. If data represents a sample, use VARA or VAR.S VARPA evaluates text values and logicals in references, unlike VARP....
where Table1 is an Excel Table that contains 100 rows of data in B5:E104. The challenge in this example is to construct the logic needed to match records based on a partial match. The FILTER function does not support wildcards, so we need to use a different approach. In this case, we are using the SEARCH function together with the ISNUMBER function like this: Here, the SEARCH function is configured to look for text entered in cell H4, inside the “Last” column in the table....
The formula is based on the AND function, configured with two conditions. The first conditions checks to see if the column date is greater than or equal to the start date: The second condition checks that the column date is less than or equal to the end date: When both conditions return true, the formula returns TRUE, triggering the blue fill for the cells in the calendar grid. Note: both conditions use mixed references to ensure that the references update correctly as conditional formatting is applied to the calendar grid....
Which returns the word “time”. Working from the inside out, we use the SUBSTITUTE function to find all spaces in the text, and replace each space with 100 spaces: So, for example, with the text string “one two three” the result is going to look like this: With hyphens representing spaces for readability. Keep in mind that there will be 100 spaces between each word. Next, the RIGHT function extracts 100 characters, starting from the right....
Note: DATEDIF automatically rounds down to the nearest whole month. To round up to the nearest month, see below. Complete whole months In the example shown, we are calculating complete whole months between a start date and an end date with the DATEDIF function. DATEDIF takes 3 arguments: start_date, end_date, and unit. In this case, we want months, so we supply “m” for unit. In the example shown, the formula in D5, copied down, is: The behavior of DATEDIF is automatic....
In the example shown, the formula in F7 is: Note: the last argument is optional, and defaults to TRUE, but I like to explicitly set the matching mode. VLOOKUP simply looks up the age and returns the group name from the 2nd column in the table. This column can contain any values that you wish. Pivot tables Pivot tables can automatically group numbers, but the VLOOKUP approach allows you to perform completely custom grouping....
Let’s take a look. Here we have a set of data that’s already formatted as an Excel Table. Let’s use this table to create a pivot table and add some fields. Since the source data is already a Table, we’ll use the Summarize With Pivot Table command, on the Table Tools Design tab. Let’s accept the defaults, and let Excel create the pivot table on a new worksheet. A new pivot table doesn’t have any fields, so our first task is to add some....
Counting is one of the most common tasks people do in Excel. It’s one of the metric that is often used to summarize the data. For example, count sales done by Bob, or sales more than 500K or quantity of Product X sold. Excel has a variety of count functions, and in most cases, these inbuilt Excel functions would suffice. Below are the count functions in Excel: COUNT – To count the number of cells that have numbers in it....
Before dynamic array formulas, counting unique values in Excel involved complex array formulas, especially if you needed to count values based on one or more conditions. Now that dynamic array formulas, and especially the UNIQUE function, are part of Excel, this is all much easier. On this first worksheet, this list of colors contains duplicates. We want to get a count of the unique colors. Now, I can easily count these values with the COUNTA function....
Let’s take a look. To find a value in Excel, use the Find and Replace dialog box. You can access this dialog using the keyboard shortcut control-F, or, by using the Find and Select menu at the far right of the Home tab on the ribbon. Let’s try looking for the name Ann. Nothing happens until we click the Find Next button. Then, each time we click, Excel finds another match....
FIND(“]”,FilePath) will return the location of the “]” character before the sheet name (let’s call this the Location). In our example FIND(“]”,FilePath) returns the value 132 since “]” is the 132nd character in the FilePath text string. LEN(FilePath) will return the total character length of the FilePath text string (let’s call this the TotalLength). In our example LEN(FilePath) returns the value 140 since there are 140 characters in the text string....
Example Generic Formula What It Does This formula will return the folder path where the current workbook is saved. How It Works CELL(“filename”,AnyCell) will return the full folder path, file name and sheet name of the referenced cell. For my workbook this was the following location. All we need to do is parse all the text up until the first square bracket [ seen above highlighted in yellow. We will first need to find the location of [ using FIND(“[“,CELL(“filename”,AnyCell))....
Here we have an example we looked at previously. With one conditional formatting rule that uses a formula, we’re able to highlight rows based on the task owner. This works well. But what if we want to highlight rows based on both priority and owner? In that case, we’ll need to extend the formula to handle 2 conditions. First, I’ll add an input cell for priority. I’m also name these both input cells to make the formula we’re going to create a little easier to read....
Let’s take a look at a few ways to do that. First, let’s take a look at the problem. Here’s a simple spreadsheet that captures hours worked in a week and displays a total. The total is calculated using the SUM function on all cells above. Let’s say you want to copy the formula that sums the hours to another location. If we just copy the original formula, then paste, it stops working properly because the cell references have been updated based on the new location....
Let’s take a look. In order to save an Excel workbook to a different file format, you need to use the Save As command. You can access Save As in several different ways. One way is to go to the File tab and click the Save As command at the upper left. The Save As dialog box allows you to choose a location, a file name, and select a file type from a long list of file formats....
Let’s take a look. The first thing to understand about selecting cells is that there is always at least one cell already selected in the worksheet. To see what is currently selected, check the name box. The name box will display a reference to the current selection. To select one cell in Excel, just click anywhere in the worksheet. Notice that the address shown in the name box is continuously updated....
VLOOKUP is one of the most useful functions in Excel, but after learning its use most people quickly realize some of its limitations. It only allows you to look up items to the right of a column with the criteria you’re looking up.It requires you to know how many columns away the results column is from the lookup column. This means if you insert a column later on your formula will break....
which returns “x” when B6 contains “red” or “green”, and an empty string ("") if not. Notice the OR function is not case-sensitive. This is an example of nesting – the OR function is nested inside the IF function. Working from the inside-out, the logical test is created with the OR function: OR will return TRUE if the value in B6 is either “red” OR “green”, and FALSE if not....
