MrExcel Excel Videos

You want VBA to write several copies of the current Excel workbook. SaveAs causes problems, because the original workbook is no longer open Instead use .SaveAsCopy to save a copy of the workbook As far as I can tell, SaveAsCopy does not exist in the Excel interface. It allows you to keep the current workbook open with the original name and path, but save extra copies with a new name. This is particularly handy if you need to remove macros from a workbook while saving. Download the code from the video at the link below. To download this workbook: https://www.mrexcel.com/download-center/2018/06/save-as-keeping-original-open.xlsm List of upcoming seminars: Excel Seminar Schedule

Sam in Nashville is filtering a pivot table If you use Date Filters or Label Filters, Excel remembers the setting and will re-apply the filter after a Refresh But… if you use the Search box to filter, Excel will not re-apply the search at a Refresh Instead, use the Label Filter for "Contains" To download this workbook: https://www.mrexcel.com/download-center/2018/05/pivot-table-search-bug.xlsx List of upcoming seminars: Excel Seminar Schedule

Why does our employee May keep sorting to the top of the pivot table? By default, pivot tables will follow the sort pattern of any custom lists. If you employ people named June or May or Wednesday, they will sort to the top of the list. File, Options, Advanced. Scroll all the way down. Edit Custom Lists. The names in the first four series can not be removed. I used to joke that one solution is to fire all the people named April or June. Right-click the pivot table and choose Options. Go to the Totals & Filters tab. Uncheck Use Custom Lists when sorting. Download the file to work along: https://www.mrexcel.com/download-center/2018/05/pivot-table-no-custom-sort.xlsx Read the article: Pivot Table No Custom Sort

A company wants to bill in quarter hours and they always want to round up to the next quarter hour. 1:42 There are 24 hours in a day. There are 24x4 or 96 quarter hours in a day. Thus, 15 minutes is equal to 1/96 2:07 Use CEILING(A2,1/96) to round up to the next higher quarter hour. 2:45 There are 24 hours in a day. There are 24x12 or 288 five-minute periods in a day. Use 1/288 to round to nearest five minutes 3:02 To round to the nearest six minutes, use 1/240 3:10 Use 1/1440 to round to nearest minute 3:33 Use FLOOR(A2,1/96) to round to the lower quarter hour. 3:47 Use MROUND(A2,1/96) to round to the nearest quarter hour. 5:26 Why a column of times do not sum correctly 6:28 Bonus tip: Which number format? 13:30 or 13:30:55 or 37:30:55...

Question from the Gulf South Council of the IMA seminar in Atlanta Hey! I have data formatted with every other row in Grey When I insert two rows, both rows are grey. I have a great solution with conditional formatting, but it requires 3rd grade math Divide the row number by 2. What is the remainder? It is going to be 0 or 1. Set up conditional formatting to check to see if =MOD(ROW(),2)=1. If it is, fill that row. It works awesomely. Feel free to download the workbook and set up a MOD function to highlight 3 rows in orange and one row in Teal It is all in the MOD function. To download this workbook: https://www.mrexcel.com/download-center/2018/05/inserting-alternating-rows.xlsx

Flo from Nashville: Can I VLOOKUP to two different tables? Look for the item in catalog 1. If it found, then great. But if it is not found, then move on and do a VLOOKUP from Catalog 2. My solution: Start with =VLOOKUP(A4,Frontlist,2,False). Wrap that VLOOKUP in the IFERROR function: =IFERROR(VLOOKUP(A4,Frontlist,2,FALSE),VLOOKUP(A4,Backlist,2,FALSE)) The article for this topic is at VLOOKUP To Two Tables You can find the link to download the file from today's video near the end of the same article.

Atlanta Power Excel Seminar: Is there a way to have Excel always shift my numeric entry so the last 2 digits appear after the decimal place? Yes - File, Options, Advanced. Automatically insert a decimal point. Choose that setting and specify the number of digits To convert all entries to thousands, change the number of digits to -3 To download this workbook: https://www.mrexcel.com/download-center/2018/05/insert-2-decimals.xlsx

Frank from NJ downloads data where the "empty" cells contain some number of spaces Frank wants to replace the spaces with truly empty cells. Writing a formula and then Paste Values does not give us empty cells (Look in the status bar to get a Count of how many non-blank cells there are) Excel really needs to offer a =NULL() function to return empty cells My solution: Filter. Choose Blanks. Select. Press Delete To download this workbook: https://www.mrexcel.com/download-center/2018/05/replace-spaces-with-empty.xlsx

Quentin needs to generate an identical survey for 1000 different customers. He wants to repeat the 8 survey questions for each customer. While you could do this with VBA or a macro, this is Power Query week, so here is the PQ method. Add an extra blank question to the questions. Make the customers in to a table. Make the questions in to a table. Add the Questions to Power Query as a Connection Only Add the Customers to Power Query. Create a new custom column where the formula is #“Query B” Expand the column in the Power Query editor Close & Load To download this workbook: https://www.mrexcel.com/download-center/2018/05/loan-survey-explosion.xlsx List of upcoming seminars: Excel Seminar Schedule