MrExcel Excel Videos

Learn Excel from MrExcel - Excel tips and tricks from Bill Jelen.
Learn Excel - Filter Subtotaled Data - Podcast 2174
R.A. asks? Can you filter data that has been subtotaled? How can you get the subtotals to appear at the top of each group? When you create the subtotals, uncheck Summary Below Data Can you use a Filter in a data set that has been subtotaled? Yes - but you should include blank cells as well How can you get the percentage calculation to work in the subtotal rows? Right now, the percentage is totaling the other rows instead of doing the calculation After adding subtotals, copy the percentage calculation from a non-subtotaled row to all other rows.
Learn Excel - Lookup Row & Sheet - Podcast 2173
Rhonda from Cincinnati: How to look up both row and worksheet? Use the Date column to figure out which sheet to use Step 1: Build a regular VLOOKUP and use FORMULATEXT to see what the reference should look like Step 2: Use Concatenation and the TEXT function to build a reference that looks like the table array reference in the formula Step 3: Build your VLOOKUP, but for the table array, use INDIRECT( results from step 2) Step 4: Copy the formula from Step 2 (without the equals sign) and paste in to the formula from step 3
Dueling Excel - VLOOKUP from Outline Data - Duel 189
From a popular certification test - how to do a VLOOKUP when column A is in outline format Advice from Bill: Anything on that test is not that difficult! Bill's Method: Do the straight VLOOKUP Wrap the VLOOKUP in the IFNA function. If you get an #N/A, then use the value from above This method only works in Excel 2013 or newer. In Excel 2010, use IFERROR instead. Mike's Method: Inside the VLOOKUP function, use the LOOKUP function to find the last text in an expanding range. LOOKUP ZZZZZZ so it will return the last non-blank item Use an expanding range to find the last text value in the column down to this point In Mike's second method, he shows where trailing spaces will cause Bill's formula to fail. Mike's second method uses ISBLANK...
Learn Excel - Pivot Timeline to Title - Podcast 2170
Nick from Utah wants to show the dates chosen from a pivot table Timeline to a readable title. Copy the pivot table that is already tied to the timeline and paste it out of view Change that pivot table to have the date field twice in the Values area Double-click each heading to get to Field Settings. Choose Min in the first cell and Max in the second cell. Use the text function to concatenate together a useful heading
Learn Excel - Subtotal by Month - Podcast 2169
Megan in Fort Myers Florida: How to add subtotals by Month when the data is stored by date? This episode shows two methods: Add a new column to the left of Date Use the TEXT function with a second argument of "MMMM YYYY" Add Subtotals by the new column To move the Subtotals back to the Data column: Collapse to #2 view, Select blanks in date column, Alt+; to select visible cells = Left Arrow Ctrl+Enter Go back to #3 View Method #2 Temporarily change number format for dates to show only month Add Subtotals by Date - you will get one total per month Change number format back to short date
Learn Excel - Shift Subtotals Right 1 Column - Podcast 2168
Rebecca in Springfield MO asks: How to move the subtotals right one column? Sort the data by account Data, Subtotals, At Each Change in Account, Sum the Amount Use the #2 group and outline button to show only the totals Select all of the blanks in one column to the left of the subtotals Use Home, Find & Select, Go To Special, Visible Cells Only or Alt+; Home, Insert, Cells, Shift Cells Right Click the #3 Group & Outline button
Dueling Excel - Sales by Region & Team - Duel 188
Build a Sales Report by Region and Team Original data has sales rep and region A second (badly shaped) table organizes the sales reps in to teams Bill method 1: Re-shape the team hierarchy data. Make both ranges into Ctrl+T tables Create a pivot table, adding the data to the data model. Pull Team from second table. Create a relationship Mike Method2: Build a SUMIFS where the Criteria2 field is an array! Pass the SUMIFS in to the SUMPRODUCT function Bill Method 3: Rearrange the hierarchy table so sales rep is on the left. Add a VLOOKUP to the original data Build a pivot table Mike Method 4: Use the Relationship icon on the Data tab of the ribbon When you create the pivot table, choose Use this Workbook's Data Model Bill Method 5: Power...
Learn Excel - Ctrl+Click to Insert Columns - Podcast 2166
I was in Lakeland Florida at Florida Southern College for an Excel seminar There are five ways to do everything in Excel. For example: If you need tiny columns between the columns, you could do Alt+I C Alt+O C W 1 Enter over and over and over But the F4 key will repeat the last action. By breaking the task in to two pieces, you can do Alt+I C once and then repeatedly hit LeftArrow F4 until all the columns are inserted Then, do Alt+O C W 1 Enter once and repeatedly hit RightArrow RightArrow F4 until all columns are smaller But Lacie had a faster way: Ctrl+Click each column and Alt+I C once That will insert a column between each column Ctrl+Click each new column and Alt+O C W 1 once You could turn this trick sideways and use in to...

Forum statistics

Threads
1,226,693
Messages
6,192,471
Members
453,726
Latest member
JoeH57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top