Transcript In this video, we’ll look at some examples of formatting axis labels. Here we have a simple set of generic shipping data. We have a date, quantity, and a field to indicate batch number. Let me insert a standard column chart, and let’s run through some options in adjusting the labels that appear in the horizontal category axis. The first thing you probably notice is that there are some gaps in the columns....
Transcript In this lesson we’ll look at how to save an Excel workbook in a different format. 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....
Transcript After you’ve selected a chart type, there are many options available to improve readability. Let’s take a look. Here we have a pivot table showing sales by Region. Let’s add a column chart. Notice our chart is using the general number format. That’s because we haven’t yet set a format in the pivot table. Let’s do that now. Notice that both the pivot table and the pivot chart pick up the currency format....
Purpose Return value Syntax =IMSEC(complex_num) complex_num - The complex number in the form “x+yi”. Using the IMSEC function The Excel IMSEC function returns the secant of a complex number. For example, given “4+3i” as input the function returns “-0.065294028-0.07522496i” as output. =IMSEC("4+3i") // returns -0.065294028-0.07522496i Explanation In math, the complex secant function is the reciprocal of the complex cosine function . In Excel, the output of the complex secant function is equivalent to the following formula....
Purpose Return value Syntax =INDIRECT(ref_text,[a1]) ref_text - A reference supplied as text. a1 - [optional] A boolean to indicate A1 or R1C1-style reference. Default is TRUE = A1 style. Using the INDIRECT function The INDIRECT function converts a text string like “Sheet1!A1” into a valid reference like =Sheet1!A1 . That sounds simple enough, but of all Excel’s many functions , INDIRECT might be the most confusing to users. Why would you use text when you can simply provide a normal reference?...
Purpose Return value Syntax =LAMBDA(parameter,...,calculation) parameter - An input value for the function. calculation - The calculation to perform as the result of the function. Must be the last argument. Using the LAMBDA function The LAMBDA function provides a way to create a custom function in Excel. Once defined and named, a LAMBDA function can be used anywhere in a workbook. LAMBDA functions can be very simple or quite complex, stringing together many Excel functions into one formula....
By default, a pivot table shows only data items that have data. When a pivot table is set up to show months, this means that months can “disappear” if the source data does not contain data in that month. In the example shown, a pivot table is used to count the rows by color. There is no data in the months of March and May, so normally these columns would not appear....
I was looking at an article on forbes.com about popular college degrees for women and men. They listed 9 degrees for both women and men (on many, many pages), and this chart is just a mash-up of the data they cited, sorted to show popular degrees for women at the top. The chart type is 100% stacked bar, and its a good example of how a bar charts can accommodate extremely long labels....
Purpose Return value Syntax =PRICEDISC(sd,md,discount,redemption,[basis]) sd - Settlement date of the security. md - Maturity date of the security. discount - Discount rate of the security. redemption - Redemption value per $100 face value. basis - [optional] Day count basis (see below, default =0). Using the PRICEDISC function The Excel PRICEDISC function returns the price per $100 face value of a discounted security. In the example shown, the formula in F5 is:...
Explanation Although Excel has a RANK function , there is no RANKIF function to perform a conditional rank. However, you can easily create a conditional RANK with the COUNTIFS function. The COUNTIFS function can perform a conditional count using two or more criteria. Criteria are entered in range/criteria pairs. In this case, the first criteria restricts the count to the same group, using the named range “groups” (C5:C14): =COUNTIFS(groups,C5) // returns 5 By itself, this will return total group members in group “A”, which is 5....
About This Shortcut This keyboard shortcut will move the current selection to the next column right. We don’t know of any Windows equivalent; let us know if you find one. About This Shortcut This keyboard shortcut will move the current selection to the previous column left. We don’t know of any Windows equivalent; let us know if you find one.
Explanation The goal is to generate a series of sequential dates with a formula. In the current version of Excel, the easiest method is to use the SEQUENCE function. SEQUENCE can return all dates at the same time into a range on the worksheet. In older versions of Excel without the SEQUENCE function, you can enter a formula, then manually copy the formula down as needed. Both approaches are explained below....
Explanation In this example, the goal is to return the smaller of two values which appear in columns B and C. Although this problem could be solved with the IF function (see below), the simplest solution is to use the MIN function. MIN function The MIN function returns the smallest numeric value in the data provided. In Excel, it’s common to use the MIN function with a range like this:...
Explanation This formula uses COUNTIF with an expanding range to first check if the current row is the first occurrence of a given invoice number: COUNTIF($B$5:B5,B5)=1 This expression only returns TRUE when this is the first occurrence of a given invoice number. If so, a SUMIF calculation is run: SUMIF($B:$B,B5,$D:$D) Here, SUMIF generates a total sum by invoice number, using the amounts in column D. If the count is not 1, the formula simply returns an empty string ("")...
Transcript When evaluating a formula, Excel follows a standard math protocol. First, any expressions in parentheses are evaluated. Next, Excel will solve for any exponents. After exponents, Excel will perform multiplication and division. Finally, Excel will solve for addition and subtraction. This order of operations can be expressed by the mnemonic phrase “Please Excuse My Dear Aunt Sally” which stands for: Parentheses Exponents Multiplication and Division Addition and Subtraction Let’s take a look....
Purpose Return value Syntax =TOROW(array,ignore,scan_by_column) array - The array to transform. ignore - Control to ignore blanks and errors. scan_by_column - Scan array by column. TRUE = by column, FALSE = by row (default). Using the TOROW function The TOROW function transforms an array or range into a single row. TOROW can scan values by row, left to right (default) or by column, top to bottom. The TOROW function takes three arguments: array , ignore , and scan_by_column ....
Explanation In this example, the goal is to use VLOOKUP to retrieve the price for a given item based on three criteria: name, size, and color, which are entered in H5:H7. For example, for a Blue Medium T-shirt, VLOOKUP should return $16.00. The VLOOKUP function does not handle multiple criteria natively. Normally VLOOKUP looks through the leftmost column in a table for a match, and returns a value from a given column in a matching row....
Explanation In geometry, the formula for calculating the volume of a cone is: The formula for calculating the volume of a cone is based on the formula for calculating the volume of a pyramid. Since the base of a cone is a circular, the formula for area of a circle is included. The Greek letter π (“pi”) represents the ratio of the circumference of a circle to its diameter. In Excel, π is represented in a formula with the PI function , which returns the number 3....
Purpose Return value Syntax =WEIBULL.DIST(x,alpha,beta,cumulative) x - The value at which to evaluate the distribution (must be ≥ 0). alpha - The shape parameter of the distribution (must be > 0). beta - The scale parameter of the distribution (must be > 0). cumulative - A logical value that determines the form of the function. If TRUE, returns the cumulative distribution function; if FALSE, returns the probability density function. Using the WEIBULL....
We asked over 800 users about their favorite Excel shortcuts in a survey. In this video, Dave shows you more than 30 of the most popular Excel shortcuts in action. He also explains why shortcuts in Excel are hard, and give you six good reasons why you should learn them anyway! Also see this video on Shortcut Recipes . The practice file is attached below. (Note: I cut down the addresses in “big data” so that the file wouldn’t be so large)....
