Transcript In this video, we’ll look at how to copy formatting from one chart to another. How can you make one chart look like another? You have several options. One option is to copy formatting, and use Paste Special. For example, let’s say we want the chart on the right to look like the chart on the left. First, select the chart on the left and copy. Next, navigate to the home tab of the ribbon and, under the Paste menu, select Paste Special....
Transcript To enter a formula that contains cell references, you have two choices. You can type the references directly, or you can point and click to cells to pick up their addresses. Let’s take a look. The most basic way to enter cell references in a formula is just to type in the references as you need them. For example, we can type the formula “=B7+D6” directly. Notice that you don’t need to worry about case....
Transcript Often, you’ll need to join together values in Excel. A good example is when you have first, last, and middle names in separate columns and you want to join these together into one name. This is referred to as “concatenation.” In this example, we have first, middle, and last names shown separately in a table. I’ll use simple concatenation to join these separate names together into a single name....
Transcript What does the INDEX function do? Unlike the MATCH function , which gets the position of an item in a list or a table, INDEX assumes you already know the position, and it gets the value of the item at that position. Let’s take a look. INDEX is a powerful and flexible function that can be used for advanced features like dynamic ranges and two-way lookups. However, in this example, we’re going to use INDEX for the simplest use case possible—retrieving items from a known position....
Transcript Pivot tables do not automatically update when the source data is changed. To update a pivot table, you need to “refresh” it. When you refresh a pivot table, you are simply asking Excel to update the Pivot Table based on the latest source data. Let’s take a look. The easiest way to update a pivot table manually is to right-click anywhere in the table and choose “Refresh” from the menu....
Transcript In this lesson we’ll look at the Fraction format. The Fraction format is made to display fractions. It can handle fractions of up to three digits, and comes with several presets for common fraction units. 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....
About This Shortcut This shortcut will increase the font size of current selection but one “step”. Steps correspond to the sizes shown in the font size menu. About This Shortcut This shortcut will decrease the font size of current selection but one “step”. Steps correspond to the sizes shown in the font size menu.
A line chart is a built-in Excel chart type, with each data series plotted as a separate line. Line charts are a good way to show change or trends over time. In contrast to column or bar charts, line charts can handle more categories and more data points without becoming too cluttered. Line charts can be customized to show or hide data markers of various shapes and sizes. Pros Simple presentation; easy to read and create Clean presentation of multiple data series with many data points Good for showing trends over periods of time Can handle positive and negative values Cons Harder to read when lines overlap frequently Line can imply more data than actually available (compared to bar or column chart) An area chart is a primary Excel chart type, with data series plotted using lines with a filled area below....
About This Shortcut This shortcut opens the Visual Basic Editor. About This Shortcut This shortcut opens the Power Query Editor. It is only available in the latest version of Excel 365 .
Explanation In this example, the goal is to parse a text string into a proper Excel time . First, note that the cells in F5:F13 are formatted as Text prior to entry . This allows the times to contain leading zeros like “083000”. Alternately, you can enter these time strings with a single quote at the start (’) to force Excel to respect them as text. Next, the time string contains 6 characters in the following format:...
Purpose Return value Syntax =QUARTILE(array,quart) array - A reference containing data to analyze. quart - The quartile value to return. Using the QUARTILE function Use the QUARTILE function to get the quartile for a given set of data. QUARTILE takes two arguments, the array containing numeric data to analyze, and quart , indicating which quartile value to return. The QUARTILE function accepts 5 values for the quart argument, as shown in the table below....
Purpose Return value Syntax =RADIANS(angle) angle - Angle in degrees to convert to radians. Using the RADIANS function The RADIANS function takes an angle in degrees and converts it to radians. Radians measure angles using the radius of a circle. For example, to convert a 45 degree angle to radians the formula is: =RADIANS(45) // Returns 0.785 or π/4 Explanation One radian is equal to the amount of rotation required to travel one radius along the circumference of the circle, as seen in the image above....
Explanation In this example, the goal is to remove empty rows from a range with a formula. One approach is to use the BYROW function to identify all non-empty rows in the range and pass this result into the FILTER function as the include argument. This is the approach used in the worksheet shown, where the formula in cell G5 is: =FILTER(B5:E16,BYROW(B5:E16,LAMBDA(row,SUM(--(row<>""))>0))) Working from the inside out, the BYROW function is used to check for non-blank rows like this:...
Explanation The ROUND function rounds a number to a given number of places. The number of places is set by the number of digits provided in the second argument ( num_digits ). For example, the formulas below round the number 5.86 to 1 and zero places: =ROUND(5.86,1) // returns 5.9 =ROUND(5.86,0) // returns 6 In the example shown, we are rounding the values in column B (which are created with the PI function ) using the numbers in column B for digits....
Transcript In this video, we’ll take a look at some of the basic file shortcuts in Excel. First, to create a new workbook in Excel, you can use Control + N on Windows and Command + N on a Mac. To access the Excel help system, use F1 on Windows, Command + / on a Mac. To open an existing workbook, you can use the File Open shortcut: Control + O in Windows, Command + O on the Mac....
Purpose Return value Syntax =SKEW.P(number1,[number2],...) number1 - A range or reference that contains numeric values. number2 - [optional] A range or reference that contains numeric values. Using the SKEW.P function The SKEW.P function returns the “skewness” of a distribution. SKEW.P measures the symmetry of a distribution. A positive skew result indicates a distribution that tails off to the right. A negative skew result indicates a distribution that tails off to the left....
Explanation The MID function returns characters using a fixed starting point and ending point. In this case, the markup consists of the html bold tag, which appears at the start of each cell and the associated closing tag, which appears at the end. The MID function has been configured to always start at 4, which effectively strips the starting tag from the value. The second argument, num_chars, is calculated by getting the total characters in the cell with LEN, then subtracting 7....
Explanation In this example, the goal is to sum Total when the corresponding Color is either “Red” or “Blue”. For convenience, all data is in an Excel Table named data . This is a tricky problem, because the solution is not obvious. The go-to function for conditional sums is the SUMIFS function . However, when using SUMIFS with multiple criteria, all conditions must be TRUE. This means that multiple conditions are joined with AND logic and there is no direct way to apply conditions with OR logic....
Explanation In this example, the goal is to sum values in matching columns and rows. Specifically, we want to sum values in data (C5:G14) where the column code is “A” and the day is “Wed”. One way to solve this problem is with the SUMPRODUCT function , which can handle array operations natively, without requiring control shift enter. In the latest version of Excel, the FILTER function is another option. Both approaches are explained below....
Explanation In this example, the goal is to count rows where the value in column one is “A” or “B” and the value in column two is “X”, “Y”, or “Z”. In the worksheet shown, we are using array constants to hold the values of interest, but the article also shows how to use cell references instead. In simple scenarios, you can use Boolean logic and the addition operator (+) to count with OR logic , but as the number of values being testing increases, the formula becomes unwieldy....
