123 useful answers · Page 4 of 6
Work through formulas, formatting, sorting, and everyday data tasks.
Search this topicPut quantities in B2:B20 and unit prices in C2:C20, then enter =SUMPRODUCT(B2:B20,C2:C20). Excel multiplies each matching pair and adds the products. Use equal-sized ranges that line up row by row. Check a two-row sample manually before using a large dataset. If your rules round individual line amounts first, use a helper calculation that makes that rounding explicit.
Use =PRODUCT(B2:D2) to multiply numeric values across a range. For 2, 3 and 4, the result is 24. Numeric zero makes the product zero, while blanks and text in referenced cells are ignored. That can hide missing inputs, so check required fields before using PRODUCT for measurements or totals where every factor must be supplied.
Use =POWER(A2,2) to square A2 or =POWER(A2,3) to cube it. The equivalent exponent notation is =A2^2. For a value of 4, the square is 16 and the cube is 64. Use parentheses for compound bases, such as =(A2+B2)^2, so Excel raises the whole sum rather than only the final term.
With year in A2, month in B2 and day in C2, enter =DATE(A2,B2,C2), then apply a date format. Use four-digit years to avoid ambiguity. DATE can roll out-of-range month or day values into another date, so it is not a strict validation test by itself. Check that the original components describe a valid intended calendar date.
Use =DATE(YEAR(A2),MONTH(A2),1) when A2 contains a real Excel date. This preserves the year and month and sets the day to one. Format the result as a date if it appears as a serial number. It is useful for monthly grouping across multiple years, where a month name alone would mix different January records together.
Try =DATEVALUE(A2), then format the result as a date. Interpretation depends on regional date conventions, so a value such as 03/04/2026 may be ambiguous. Prefer an import process with an explicit date order or separate year, month and day components when accuracy matters. Check several known dates before converting an entire column.
Use =DAY(A2) to get a number from 1 through 31 from a genuine Excel date. It returns the day within the month, not the weekday. For September 21, 2026, the result is 21. If A2 contains text that merely looks like a date, convert or re-import it first instead of assuming every regional format will be recognized.
Use =MONTH(A2) for a genuine Excel date; September returns 9. The output is numeric and can be useful for filtering or grouping. It does not include the year, so pairing it with YEAR(A2) is important for data covering several years. Do not feed MONTH a standalone month name and assume Excel will interpret it as a complete date.
Use =YEAR(A2) to return the calendar year as a number. If A2 holds September 21, 2026, the result is 2026. This differs from formatting the original date to display only its year, which leaves the underlying full date intact. Check that imported dates are stored as date values rather than text before copying the formula down.
Enter =TODAY() and apply a date format. It updates when the workbook recalculates, so it is useful for current-date comparisons but unsuitable as a permanent record of when a task was entered. To preserve an event date, enter a fixed date instead. A workbook using manual calculation may not refresh the displayed date until recalculation occurs.
Enter =NOW() and apply a format that displays both date and time. NOW reflects the time when Excel recalculates; it is not a continuously ticking clock. It also changes on later recalculations, so it cannot serve as an immutable timestamp. For a record of when a form was submitted, use a stored timestamp from the submission process.
Use =EDATE(A2,3), then format the result as a date. Unlike adding 90 days, EDATE moves by calendar months, whose lengths differ. For dates near month-end, the resulting month may not contain the same day number, so check boundary cases that matter to your schedule. Use a negative month count to move backward.
Use =EOMONTH(A2,0) for the final day of A2's month. Change the second argument to 1 for the following month or -1 for the previous month. Format the result as a date. This handles different month lengths without a hard-coded list of 28, 29, 30 and 31 days, but A2 still needs to contain a valid date value.
Use =WEEKDAY(A2,2). The second argument selects Monday = 1 through Sunday = 7. If you omit it, the default numbering is different, which can shift every weekday-based rule. Test a known Monday and Sunday before using the numbers in a schedule. The function returns a number; use a separate label or date format if readers need weekday names.
Use =NETWORKDAYS(A2,B2) for a standard Monday-to-Friday calendar. It counts eligible start and end dates, so a single Monday-to-Monday period counts as one workday. Add a holiday list as the third argument when needed. If your workweek has different weekend days, choose NETWORKDAYS.INTL instead and define the pattern explicitly.
Store holiday dates in H2:H12 and use =NETWORKDAYS(A2,B2,$H$2:$H$12). Excel omits listed holidays as well as standard Saturday and Sunday weekends. Use actual date values and maintain the holiday list for the relevant year and location. The formula does not automatically know your organization's holidays, closures or part-day working rules.
Use =WORKDAY(A2,5,$H$2:$H$12), where H2:H12 contains excluded holidays. WORKDAY moves forward five eligible working days after the start date and assumes Saturday and Sunday weekends. Format the result as a date. Clarify whether your real deadline rule counts the starting day; the function's offset convention may differ from the wording of a policy.
Use =TIME(A2,B2,C2) when the three cells contain hour, minute and second components of a time of day. Format the result as a time. TIME wraps values into a 24-hour day, so it is not the right way to preserve a duration of 30 hours. For durations, build the numeric day fraction directly and use an elapsed-time format.
Use =HOUR(A2) to return the hour component, from 0 to 23. It extracts the time-of-day hour even when A2 also contains a date. It does not calculate total elapsed hours across several days. For an elapsed duration stored as days, multiply by 24 instead and use a numeric format suited to the result.
Use =MINUTE(A2) to return the minute component, from 0 to 59. For 2:35 PM, it returns 35, not 875 total minutes after midnight. If you need an elapsed duration in minutes, multiply the underlying day value by 1,440 instead. Decide whether you want one clock component or a total duration before choosing the formula.
Use =SECOND(A2) for the seconds component, from 0 to 59. A value representing 1 minute 20 seconds returns 20, not 80. For total elapsed seconds, multiply a duration stored in days by 86,400 and choose appropriate rounding. Check how the source recorded fractional seconds if exact timing precision matters.
Use =VALUE(A2) in a helper column for text that follows a number format Excel recognizes. Review the result before replacing the source. Currency marks, separators and regional conventions can affect interpretation, while identifiers with leading zeros may need to remain text. Test an ordinary number, a negative value and a decimal from your import rather than assuming one successful row proves the entire column.
In dynamic-array Excel, enter =TRANSPOSE(B2:F2) in an empty area. The five horizontal values appear vertically and stay linked to the source. Leave enough empty destination cells. Older Excel versions may require selecting the destination range and entering an array formula. Use Paste Special Transpose instead if you want a one-time independent copy rather than a live formula result.
Use =SEQUENCE(20) in a dynamic-array version of Excel to generate 1 through 20 vertically. For ten numbers starting at 100 and increasing by 5, use =SEQUENCE(10,1,100,5). Keep the output cells empty so the list can expand. These numbers recalculate as a formula result; they are not permanent record identifiers unless you deliberately store them as values.
Source checks for these answers: September 21, 2026. Product instructions and local requirements can vary.