Explanation In the Excel time system, one 24-hour day is equal to 1. This means times and hours are fractional values of 1, as shown in the table below: Hours Time Fraction Value 1 1:00 AM 1/24 0.04167 3 3:00 AM 3/24 0.125 6 6:00 AM 6/24 0.25 4 4:00 AM 4/24 0.167 8 8:00 AM 8/24 0.333 12 12:00 PM 12/24 0.5 18 6:00 PM 18/24 0.75 21 9:00 PM 21/24 0....
About This Shortcut Copy puts everything on the clipboard: text, formulas, formatting, borders, fills, etc. Use Paste Special to selectively paste only parts of what was copied. On the Mac, Ctrl + C also works About This Shortcut This shortcut will repeat the last action again, when available. For example if you just applied a border and red fill to one cell, you can use this shortcut to apply the same formatting to one or more selected cells....
Explanation The COUNTIFS function takes one or more criteria, entered as range/criteria pairs. In this example, the first range/criteria pair is: B5:B11,">="&E5 Matching any time greater than or equal to the time E5 (5:00). The second range/criteria pair is: B5:B11,"<"&E6 Matching any time less than the time in E6 (6:30). With hard-coded values The formula in E7 could be written with hard-coded time values as follows: =COUNTIFS(B5:B11,">=5:00",B5:B11,"<6:30") Excel translates a string like “5:00” into the correct numeric time value....
Transcript In this video we’ll look at how to create a dynamic reference to a worksheet in a formula. Sometimes you want to reference a worksheet dynamically in a formula, so it can be easily changed. In this workbook we have five weeks of test scores, each in the same format. Let’s assume we want to build a simple dashboard that shows the Maximum score, the Minimum score, and the Average score for whichever worksheet a user selects....
Purpose Return value Syntax =DB(cost,salvage,life,period,[month]) cost - Initial cost of asset. salvage - Asset value at the end of the depreciation. life - Periods over which asset is depreciated. period - Period to calculation depreciation for. month - [optional] Number of months in the first year. Defaults to 12. Using the DB function The Excel DB function returns the depreciation of an asset for a specified period using the fixed-declining balance method....
About This Shortcut Use this shortcut to display the Paste Special dialog box. Paste Special is the gateway into many powerful operations, including paste Values, which you probably use every day. Note that this shortcut only works when data has been copied to the clipboard. On Windows, once you have the Paste Special Dialog open, you can type a letter to select the particular command you want, for example: F = Formula V = Values T = Formats C = Comments N = Validation H = All using source theme X = All except borders W = Column widths R = Formulas and number formats U = Values and number formats D = Add S = Subtract M = Multiply I = Divide B = Skip blanks E = Transpose About This Shortcut This shortcut will display the Find dialog box....
Purpose Return value Syntax =DPRODUCT(database,field,criteria) database - Database range including headers. field - Field name or index to count. criteria - Criteria range including headers. Using the DPRODUCT function The Excel DPRODUCT function calculates the product of values in a set of records that match criteria. The values to multiply are extracted from a given field in the database, specified as an argument. The database argument is a range of cells that includes field headers, field is the name or index of the field to query, and criteria is a range of cells with headers that match those in database....
In Excel’s date system, dates are serial numbers and times are fractional parts of one day . When a value contains both a date and a time together, it is sometimes called a “datetime”. For example, the date November 2, 2021 corresponds to the serial number 44502. If we create this date with the DATE function, it returns 44502: =DATE(2021,11,2) // returns 44502 When a date format is applied to this value in a cell, the number 44502 can be displayed as “November 2, 2021”, “2-Nov-21”, etc....
Every once in a while, you might find Excel behaving in a bizarre or unexpected way. One example is when you accidentally trigger the scroll lock feature . Another example is when one or more formulas suddenly stops working. Instead of a result, you see only a formula, as in the screen below: The VLOOKUP formula is correct, why no result? This can be very confusing, and you might think you’ve somehow broken your spreadsheet....
Explanation In this example, the goal is to compare the values in two different lists, then extract the values that appear in both lists into a third list as shown in the worksheet above. The values for List 1 appear in column B, and the values for List 2 appear in column D. Although we have a list of fruits in this example, the same approach can be applied to names, places, products, and so on....
Purpose Return value Syntax =FLOOR.MATH(number,[significance],[mode]) number - The number that should be rounded. significance - [optional] Multiple to use when rounding. Default is 1. mode - [optional] For rounding negative numbers toward or away from zero. Default is 0. Using the FLOOR.MATH function The Excel FLOOR.MATH function rounds a number down to a given multiple, where multiple is provided as the significance argument. If the number is already an exact multiple, no rounding occurs and the original number is returned....
Details The problem The data below shows cups of coffee sold at a small kiosk for a week at different hours of the day. The times in column B are valid Excel times. The challenge What formula in cell I5 will correctly sum the total cups sold after 12:00 PM on Tuesday and Thursday? Relevant cells are shaded in green. For your convenience, the following named ranges are available: data = C5:G14 times = B5:B14 days = C4:G4...
Details A long-time reader sent me an interesting problem this past week. The goal is to end up with a text string like “MWF” for Monday, Wednesday, Friday. The problem is that the weekdays are input as yes/no abbreviations like “NYNYNYN” for “MWF”. Challenge What formula will translate the “N” and “Y” to weekday abbreviations as shown in the screenshot above? Extra points for style and elegance, but workhorse solutions are fine, too :)...
Details The goal is to find all names for a given team and return them in a single cell as a comma‑separated list. This is basically a lookup problem, but the twist is that we want to return multiple matches for each team, and we want everything in one cell. A formula like this is useful in many scenarios, including: List products by category. List classes by student. List the staff by project....
Explanation This formula calculates total working hours between two dates and times, that occur between a “lower” and “upper” time. In the example shown, the lower time is 9:00 AM and the upper time is 5:00 PM. These appear in the formula as the named ranges “lower” and “upper”. The logic of the formula is to calculate all possible working hours between the start and end dates, inclusive, then back out any hours on the start date that occur between the start time and lower time, and any hours on the end date that occur between the end time and the upper time....
About This Shortcut Move to the previous workbook window. This shortcut “cycles” through open Excel workbooks in a “reverse” direction. On Windows, you can also use Ctrl + F6 to move to the next workbook and Shift + Ctrl + F6 to move to the previous open workbook. On a Mac, the shortcut for cycling in reverse between open windows in an application is Command + Shift + . The is the back-tick or grave accent key, which is usually found above the Tab key....
About This Shortcut This shortcut will group selected rows or selected columns. Select whole rows or columns first before using. About This Shortcut This shortcut will ungroup selected rows or selected columns. Select whole rows or columns first before using.
Transcript Once you’ve added a conditional formatting rule , you’re free to add more rules as you wish. In many cases you’ll want to add additional rules to trigger different formatting based on other conditions you’re interested in highlighting. Let’s take a look. Here we have the test score table we looked at previously. Currently, there is one conditional formatting rule applied to all test scores in the table. This rule applies a conditional format of a green fill with white text when the test score is greater than 95....
In this video, we look at how to use functions like AND and OR inside the IF function. You can download the worksheet I used in the training below. More formula videos in this series: Excel formulas - 5 ways to use VLOOKUP How to build logical formulas How to build a complex formula (or not) 20+ formula tips More formula training We cover many more formula techniques in our premium course on Excel formulas, Core Formula ....
Transcript In this video we’ll look at how to calculate the number of years or months between dates using a function called DATEDIF and a function called YEARFRAC . The DATEDIF function is a “compatibility” function that comes from Lotus 1-2-3. For reasons that are unknown, it’s only documented in Excel 2000, but you can use it in your formulas in all Excel versions since that time. DATEDIF takes three arguments: start date, end date, and unit....
