Question: What formula tells you if A1 contains the text “apple”? This is a surprisingly tricky problem in Excel. The “obvious” answer is to use the FIND function to “look” for the text, like this: =FIND("apple",A1) Then, if you want a TRUE/FALSE result, add the IF function : =IF(FIND("apple",A1),TRUE) This works great if “apple” is found – FIND returns a number to indicate the position, and IF calls it good and returns TRUE....

Transcript The ability to group data is one of the most powerful and useful features in a pivot table. And although pivot tables can automatically group things like dates, times, and numbers, you can also manually group data into your own groups. Let’s take a look. This pivot table shows a breakdown of sales and orders by product. Let’s use manual grouping to organize these products into 2 custom groups. When you group items manually, hold down the Control-key and select each item that you want to include in the first group....

Transcript In this lesson, we’ll look at how to hide and unhide worksheets. You might want to hide worksheets that contain calculations, notes, or lookup data that is not normally needed. Let’s take a look. As you build more complicated workbooks, you might want to hide certain worksheets to keep things simple. Here we have a workbook that contains one worksheet for users to enter data and a reference worksheet that contains a lookup table....

Transcript When you create a pivot table, Excel creates a duplicate of the data and stores it in something called a pivot cache. A pivot cache is what makes exploring data with a pivot table fun and snappy. Because a pivot cache is a true copy of the source data, you can remove the source data from your workbook if you like. Let’s take a look. Here we have an Excel table that contains almost 3000 rows....

Transcript Sometimes you need to remove rows from a list or a table that are missing values. You could delete the rows one by one, but that will take a long time if you have a big list. In a previous tip, we showed you how to delete blank rows. In today’s ExcelJet tip, we’ll show you a cool way to delete rows that are missing values in one step; even when your list contains hundreds or thousands of rows....

Transcript In this video, we’ll look at how to sort an Excel Table. Once you have an Excel Table, it’s very easy to sort the columns. First, let’s do a quick tour of where you can find sorting controls in Excel: • On the home tab of the ribbon, you’ll find sorting options at the far right • On the Data tab, you’ll find also find 3 buttons for sorting • You can also right-click in a column and find a sort menu • Finally, when you click a filter button in a table, you’ll see sorting commands at the top....

Transcript In this lesson we’ll look at how to sort data in a table based on values in more than one column. In Excel, this is referred to as a “custom sort.” Let’s take a look. Here we have a table that contains monthly sales data for a list of customers. In addition to monthly sales, it includes the customer name, city, state, date of first order, and total orders....

Transcript When using color scales in conditional formatting, Excel assigns one color to the lowest value, and another to the highest value. Other values are assigned a weighted blend of color. This makes it easy to see general patterns in data, especially with a large data set. Let’s take a look. Here we have a large table that shows average monthly temperatures for Salt Lake City from 1949 through 2012. Let’s use a color scale to help visualize the numbers....

Explanation The goal of this formula is to verify whether the values in column B follow the format xx-xxxx-xxx, where “x” represents any single character. The IF function doesn’t support wildcards directly, so we can’t use IF by itself. Instead, we can combine the IF function with the COUNTIF function, which does support wildcards. Excel wildcards Excel supports three wildcards that can be used in formulas: Asterisk (*) - zero or more characters Question mark (?...

Explanation This formula uses -1 for match type to allow an approximate match on values sorted in descending order. The MATCH part of the formula looks like this: MATCH(F4,B5:B9,-1) Using the lookup value in cell F4, MATCH finds the first value in B5:B9 that is greater than or equal to the lookup value. If an exact match is found, MATCH returns the relative row number for that match. When no exact match is found, MATCH continues through the values in B5:B9 until a smaller value is found, then it “steps back” and returns the previous row number....

Purpose Return value Syntax =LINEST(known_ys,[known_xs],[const],[stats]) known_ys - An array or range of dependent y values. known_xs - [optional] An array or range of independent x values. const - [optional] Boolean - normal or force the constant b to equal 0. Default is TRUE = normal calculation. stats - [optional] Boolean - return additional statistics. Default is FALSE = slope and intercept only. Using the LINEST function The LINEST function returns statistics for a best fit straight line through supplied x and y values....

Explanation This formula uses the value in cell F7 for a lookup value, the range B6:C10 for the lookup table, the number 2 to indicate “2nd column”, and zero as the last argument to force an exact match. Although in this case we are mapping text values to numeric outputs, the same formula can handle text to text, or numbers to text. Explanation The core of this formula is the COUNTA function, configured with an expanding range like this:...

Explanation This formula is a standard version of INDEX + MATCH with a small twist. Working from the inside out, MATCH is used find the correct row number for the value in F4, 2100. Without the third argument, match_type, defined, MATCH defaults to approximate match and returns 2. The small twist is that we add 1 to this result to override the matched result and return 3 as the row number for INDEX....

Purpose Return value Syntax =MEDIAN(number1,[number2],...) number1 - A number or cell reference that refers to numeric values. number2 - [optional] A number or cell reference that refers to numeric values. Using the MEDIAN function The MEDIAN function returns the median (middle number) in a set of data. The calculation performed by MEDIAN varies according to the number of numeric values provided. When the number is odd, MEDIAN returns the middle number in the group....

Purpose Return value Syntax =NORMSDIST(z) z - Numeric z-score value. Using the NORMSDIST function The NORMSDIST function returns values for the standard normal cumulative distribution function (CDF). The function expects standardized input in the form of a z-score value, which represents how far a value is from the mean of a distribution in terms of the standard deviation. For better accuracy and consistency with other modern statistical functions, it is recommended to use the NORM....

About This Shortcut This shortcut display the Open File dialog box. About This Shortcut This shortcut display the Save File dialog box.

Explanation In this example, the goal is to pad a number with zeros. To illustrate how Excel functions can be combined, the number of zeros to use is variable and comes from column C. The formula used to solve this problem combines the TEXT function and the REPT function . Fixed number The TEXT function returns a number formatted as text, using the number format provided. The TEXT function can apply number formats of any kind to numbers....

The Pie Chart is a primary chart type in Excel. Pie charts are meant to express a “part to whole” relationship, where all pieces together represent 100%. Pie charts work best to display data with a small number of categories (2-5). For example, survey questions in yes/no format, data split by gender (male/female), new and returning visitors to a website, etc. Pie charts should be avoided when there are many categories, or when categories do not total 100%....

To build a pivot table to summarize data by month, you can use the date grouping feature. In the example shown, the pivot table is uses the Date field to automatically group sales data by month. Pivot Table Fields In the pivot table shown, there are three fields, Name, Date, and Sales. Name is a Row field, Date is a Column field grouped by month, and Sales is a Value field with the Accounting number format applied....

Transcript It’s not very exciting, but it’s important to have a good grasp of the terminology used with pivot tables as you master this important tool. In this lesson, we’ll walk you quickly through the most important language used to describe and operate pivot tables. Let’s take a look. Let’s start first with the data that goes into a pivot table. This data is referred to as Source data. Source data contains rows and columns, and each column represents a Field available in the pivot table....