MrExcel Excel Videos

Why can't Excel correctly sum a simple timesheet in Excel? This video shows method 1: Choose the crazy 37:30:55 number format. Method 2: Convert the times to hours when you subtract start time from end time. This video answers all of these common search terms: excel how to calculate sum of time hot to do a times sum in excel how do find the sum of time in excel how do i sum time in excel ? how do you format a cell in excel to sum time youtube how do you sum time format in excel how do you sum time in excel how do you sum time inexcel how to add sum of time in excel how to add up the sum of times of a column in excel how to do a time sum in excel how to find the sum in excel for time how to get sum of time in excel how to get the sum of...

Some call it a 3D reference or a spearing formula. If your Excel worksheets are all perfectly lined up, you can use this formula to sum across worksheets in Excel. But if your worksheets are not in the same sequence, then you can use TOCOL, SORT, UNIQUE, and SUMIFS to solve the problem. This video answers these search terms: how ot easy sum tabs in excel how sum across multiple sheets in excel how to calculate sum from other tabs in excel how to do sum across two worksheets in excel how to do sum of cells in multiple sheet in excel how to get multi excel sheets to sum how to get sum from multiple sheets in excel how to sum across excel tabs how to sum an entire workbook in excel how to sum cells from different excel sheets how to sum...

One of these four shortcut keys stops working in Windows versions of Excel: Ctrl+9 to hide rows Ctrl+Shift+9 to Unhide rows Ctrl+0 to hide columns Ctrl+Shift+0 to unhide columns If the last one is not working for you, it is because the Windows shortcut to switch language is assigned to Ctrl+Shift. This video shows the Control Panel setting to make Ctrl+Shift+0 or Ctrl+Shift+) start unhiding columns again.

Today's question from Bill. Did you know that Excel's Advanced Filter is returning partial matches? A phrase in the criteria range is treated as Phrase*. This is an annoying problem. Excel Help sort of alludes to this problem and suggests a formula in the criteria range of Advanced Filter. But if you try to automate the creation of those formulas in the criteria range, it does not work. In this video, a convoluted set of steps to automate formula creation with a carat in place of the equals sign, then Replace to change the text into a formula. This allows the Advanced Filter to work. This all begs the question: Why am I trying to use Advanced Filter at all? How about IFERROR(SIGN(MATCH))) and filter that column to 1? Table of...

A question from my Daytona Beach seminar last week: We know XLOOKUP can find the number just larger or just smaller than a number. But does this also work with Text? How about with Symbols? In this video, Bill Jelen shows how the approximate match works with XLOOKUP. Unlike MATCH or VLOOKUP, the table does not have to be sorted. You can find a value just smaller or just larger than the search value. This also works with Text, although it is case insensitive. And, it works with symbols, but in an unexpected sequence.

Did I know this? The Excel Status Bar Statistics pick up the number format of the active cell in the grid. This is a useful feature, and not a bug. But it could freak someone out when you get different answers depending on if you select from bottom to top or top to bottom. Also in this episode: the four zones of Custom Number Formats. And, the winner(s) of a free Excel PDF.

Diane has a fairly complex pivot table. She is using GETPIVOTDATA to pull data from the pivot tables to a summary report in another workbook. In another section of the report, she needs to pull YTD. But this complicates the formula, because she has to add an expanding range as the months continue. I mentioned that she could create a second pivot table that shows the sales numbers as YTD numbers. This video shows how to copy the first pivot table and how to use Running Total In to convert from monthly sales to YTD Sales.

how do i copy only subtotals in excel how to copy excel subtotals only how to copy just subtotals in excel how to only copy subtotals from excel how to extract excel subtotals how to extract subtotal from excel

Thanks for this tip from Paul A who was in my Akron seminar. Paul uses a function like SUM(XLOOKUP:XLOOKUP) to return the total of all numbers from a start date through an end date.