Here we have data for the top 10 islands in the Caribbean by population. Let me insert a standard column chart and let’s look at how Excel plots the data. When Excel plots data in a column chart, the labels run from left to right to left. In this case, the first column is Cuba, and the last is Barbados, so the columns match the order of the source data moving moving top to bottom....
In this lesson we’ll look at several ways to save a document in Excel: The Quick Access toolbar The keyboard shortcut Control-S The Save button on the File tab Let’s take a look. After you’ve made changes to an Excel workbook you need to save the file to save your changes. You should get in the habit of saving your work on a regular basis so that you don’t lose work in the event of a system problem....
Let’s take a look. Here we have a simple table that tracks the progress of a group of people towards a fundraising goal. Column C shows that each person has a goal of $3,000. And Column D shows the current funds raised per person. Let’s use conditional formatting to display progress as a data bar. As always, the first step is to select the cells we’d like to format. In the Conditional Formatting menu, data bars are a main category....
Let’s take a look. In column B of our table we have a set of numbers in General format that can be expressed as fractions. Let’s first copy these numbers to the rest of our table. Now let’s apply the Fraction format in columns C through G using the Number Format menu on the ribbon. As we can see, Excel converts most of the values to fractions. However, some numbers aren’t converted, while others are just a close match....
This is an array formula and must be entered with control + shift + enter Because we give EXACT a range of values in the first argument, we get back an array result containing TRUE FALSE values: This array goes into the AND function, which returns TRUE only if all values in the array are TRUE. Ignore empty cells To ignore empty cells, but still treat non-empty cells in a case-sensitive manner, you can use a version of the formula based on SUMPRODUCT: Here, we count exact matches using the same EXACT formula above, get a total count with SUMPRODUCT, and compare the result to a count of all non-empty cells, determined by COUNTA....
In the example shown, the formula in B5 is: This formula is then copied down from B5 to B11. The result is random dates between Jan 1, 2016 and Dec 31, 2016 (random dates in the year 2016). Notes: Random workdays To generate random workdays, you can add the WORKDAY function like this: The WORKDAY function ensures that the date returned is a working day, and not a weekend of (optionally) a holiday....
When copied down the column, this formula will generate a random group (A, B, or C) for each person in the list. Note: this is a random approach that will allow groups of difference sizes. If you need a formula approach to randomly assign people to groups of a fixed size, see this formula and this video overview. So, in this formula, RANDBETWEEN generates a number between 1 and 3, this number is used to choose a group from the 3 following values: “A”,“B”,“C”....
When you working with borders in Excel, you may want to turn off gridlines so you can more easily see what you’re doing. In Windows, you can turn gridlines on and off with Alt W + VG On a mac, tick the setting on the Layout tab of the ribbon. So, first off, you can remove all borders with Control + Shift + _ in Windows, Command + Option + _ on a Mac....
This worksheet is a simple model that compares the cost of buying coffee at a coffee shop vs. making coffee at home. There’s very little formatting, so I’ll use a number of shortcuts to clean thing up. First, I’ll turn off gridlines to make borders easier to see. I want to change all text to a dark gray to match the chart. SO I’ll select all, then use Format Cells....
With the number 3 in cell F5 for n, the result is 70. Example formula In the example shown, the formula in cell F6 is: At a high level, this formula uses the FILTER function to extract values associated with every nth row of the data, and the SUM function to sum the values extracted. Extracting data Working from the inside out, the first step in this problem is to collect the data that should be summed....
Where Table1 is an Excel Table as seen in the screen shot. In the example shown, we are using the SUMIFS function to “look up” the price of an item based on the item name, color, and size. The inputs for these criteria are the cells H5, H6, and H7. Inside the SUMIFS function, the sum range is supplied as the “Price” column in Table1: Criteria are supplied in 3 range/criteria pairs as follows: With this configuration, the SUMIFs function finds matching values in the “Price” column and returns the sum of matching prices for the specific criteria entered in H5:H7....
where weights is the named range I5:K5. In the worksheet shown, scores for 3 tests appear in columns C through E, and weights are in the named range weights (I5:K5). The formula in cell G5 is: Working from the inside out, we first use the SUMPRODUCT function to multiply weights by corresponding scores and sum the result: SUMPRODUCT first multiplies corresponding elements of the two arrays together, then returns the sum of the product: The result is then divided by the sum of the weights: As the formula is copied down column G, the named range weights I5:K5 does not change, since it behaves like an absolute reference....
Let’s take a look. Every cell in an Excel worksheet has a unique address. The address of each cell is defined by its location on the grid. The address “B7” refers to the cell in the seventh row of column B. The address “D6” refers to the cell in the sixth row of column D, and so on. To check the address of any cell, just watch the Name Box as you select different cells....
which returns 3,517,424, the population for Berlin from column D. The lookup_value comes from cell G4 The lookup_array is the range B5:B18, which contains City names The return_array is D5:D18, which contains Population The match_mode is not provided and defaults to 0 (exact match) The search_mode is not provided and defaults to 1 (first to last) To return County instead of population, only the return array is changed. The formula in G6 is:...
which returns 0347, the order number of the first record that meets supplied criteria. Note XLOOKUP is not case-sensitive. In the example shown, we are looking for the order number of the first order to Chicago over $250. We are constructing a lookup array using the following expression and boolean logic: When this expression is evaluated, we first get two arrays of TRUE FALSE values like this: When the two arrays are multiplied by one another, the math operation results in a single array of 1’s and 0’s like this: We now have the following formula, and you can see why we are using 1 for the lookup value: XLOOKUP matches the 1 in 8th position, and returns the correponding 8th value from B5:B14, which is 0347....
This example uses a combo chart based on a column chart to plot daily sales and an XY scatter chart to plot the target. The trick is to plot just one point in the XY scatter chart, then use error bars to create a continuous line that extends across the entire plot area. Data The data used to build this chart is shown below: How to create this chart From this point, you have the basic column chart with edge-to-edge target line....
Results will update automatically when values in column B change. To convert pounds to kilograms, the formula used is in C5, copied down, is: Note: CONVERT is case-sensitive, so the text values used for units must match exactly. Kilograms to pounds To convert from kilograms to pounds, the formula would be: Pounds to Stones To convert pounds to kilograms, the formula used in D5, copied down, is Table set-up The table in the example shown uses formulas to make it easy to change the staring point and range of the values in column B....
Excel has a great tool for visualizing data called Conditional Formatting. If you work with data in Excel (and who doesn’t these days?) you’ll find it incredibly useful. By creating simple rules that highlight just the data you are interested in, you can spot key information very quickly. To help get you started, and to give you some inspiration, here are some cool ways that you can use Excel conditional formatting to help you understand data faster....
where “color” (C5:C15) and “qty” (D5:D15) are named ranges. Example When F5 is selected, the following dropdown appears: When the user makes a selection, the correct sum is returned. The named ranges “color” (C5:C15) and “qty” (D5:D15) are for convenience only. The formula in G5 performs a conditional sum based on the current dropdown selection in F5. The outermost function is an IF statement, which checks if the selection is “all”: If so, the formula returns the sum of quantity column as a final result....
Because dates are just numbers, you can easily perform arithmetic on dates. For example, with the date June 1, 2020 in A1, you can add 10 days like this: And subtract 7 days: Date formats Because Excel dates are serial numbers, you’ll sometimes see the raw numbers on a worksheet when you expect a date. To display date values in a human-readable date format, apply a number format of your choice....
