MrExcel Excel Videos

You have 100 sales leads in column A. You need to randomly assign those leads to 10 sales reps, but you want everyone to have the same number of leads. Bill works through two different methods in this episode.

Inviting all SoCal Excellers to an after-work meetup in Santa Monica California on Wednesday July 31, 2013. If you can't attend, virtually drop by with Twitter hastag #XLSoCal

Melika tweets that the colors used in the Cell Styles Dropdown in Excel 2013 have changed. What happened to the purple that used to be there? In Episode 1761, see how to quickly bring the Excel 2010 colors back. Easy to do, once you know where they hid the selection.

Today, Mike and Bill attempt to sum the top 2 records that match a set of criteria. Advanced Filter? Array Formula? Which will they use?

Michael is trying to use the Excel macro recorder in a clever way today. He has a formula that calculates a path and file name. In the macro recorder, he uses F2, Ctrl+Shift+Home, Ctrl+C and Esc to get that name on the clipboard. He then pastes it into the SaveAs box. Works great in Excel, but the macro recorder won't handle it. In this episode, you will see how to take the recorded code and modify it to Save As with the result of the formula.

To try the workbook in the Excel web app, visit: Microsoft OneDrive - Access files anywhere. Create docs with free Office Online. One of the remaining limitations of the Excel Web App is that we still can not have macros run when the file is open in a browser. Today, for a simple Excel Web App workbook, I needed a visual way to encourage people to click a button to force a recalc. (The workbook in question is designed to help those betting on the name for the new royal baby to come up with choices.) Today, a nice visual button in the Excel web app to force a recalc.

Tony wants to assign colors to each bubble in a chart based on text he has in a column in Excel. Learn how to use the macro recorder to learn the correct properties for adjusting the bubble color. Also - this is beyond what Tony asked, but the macro also correctly labels each bubble using text in column A.

Michael has an Excel workbook for printing statements. When the customer changes, there are a different number of records visible thanks to some fairly complex array formulas. How can he adjust the print range to automatically include extra data? Today's episode sets up a print range that dynamically resizes using the OFFSET function.

When a baseball pitcher is in the game for 7 and 1/3 innings, the stats books show this as 7.1 innings. But to calculate ERA, you need this to calculate as 7.333. Is there a way in Excel to easily convert between 7.1 and 7.333? It turns out Excel has a built-in function for this!