MrExcel Excel Videos

Write one query to load one web page. Then, turn that query into a function and use Power Query to load many web pages. Today's trick is adapted from the M is for Data Monkey book Build a query to get data from one web page Edit the query to change it into a function with VariableName before Let Change the hard-coded URL to VariableName Rename the query to fxWeather Close & Load. The data will disappear. Use Excel Trickery to create a table of all URL's Create a query from that table. Add a new column of Weather = fxWeather Expand the column. Uncheck Prefix Amazing!

More than you ever wanted to know about Shapes in Excel! Today's trick from Sam Radakovitz on the Excel team Shapes in Excel can be formatted with special formats Rotate Change inflection points Power Point offers a few tricks that Excel does not offer You can merge shapes in Power Point You can subtract shapes in Power Point You can use the eye dropper to color shapes

Today's trick is from Katie Sullivan on the Word team There are a few cases where Microsoft Word can do things better than Excel It is possible in Excel to format part of a cell by selecting a word in Edit mode and applying formatting. But, if you use Find & Replace and anything is changed, that formatting is wiped out. Copy from Excel. Paste to Word. Do the Replace there. Copy from Word and Paste Back to Excel. The formatting stays.

You want to get data from a table in a PDF file to Excel. Exporting Excel data as a PDF Today's trick is from Katie Sullivan on the Word team There are a few cases where Microsoft Word can do things better than Excel One of those is pasting data from PDF files Paste to Word Copy the data from Word Paste to Excel In the recap, a way in Acrobat to Copy with Formatting and it will paste half-correctly to Excel

Today's trick is from Katie Sullivan on the Word team There are a few cases where Microsoft Word can do things better than Excel One of those is adding bullets Sure - Excel can add a bullet character, using Alt+0149 This would be tedious to manually type this before each line Or, you can add a custom number format of • @ But for a wide variety of bullets, copy the data to Word, apply the bullet, and then copy back to Excel. You might use the Excel Reduce Indent button a few times

Today's trick is from Katie Sullivan on the Word team There are a few cases where Microsoft Word can do things better than Excel One of those is changing case. If you needed to convert all of this text to Upper case, you could add a helper column and use UPPER, plus then Copy & Paste Values. The video shows how to right-drag a selection and choose Copy Here as Values Only But, you can also round-trip the data to Word In Word, there is a choice to convert text to UPPER, lower, Sentence, tOGGLE cASE, or Proper Case

Today's trick is from Sam Radakovitz on the Excel team Sam's signature formatting is white Segoe UI on Excel Green RGB(26,116,61) Creating your own icon set to show items in top 25% Creating a formula-based conditional formatting to highlight entire row Use Filter dropdown to filter by color or by icon Right-click to filter by color or icon Right-click to sort by filter or icon Idea to allow filtering by two colors Vote at mrx.cl/filtertwocolors

OFFSET is a flexible function. It can be used to point to a variable top left cell, and then point to a range that has a variable shape. The problem with OFFSET is that it is volatile. Volatile functions calculate at every calculation. Instead of OFFSET, you can use INDEX(): or :INDEX() When INDEX is next to a :, it will return a cell address instead of the value at that cell. INDEX is not volatile

No one bothered to use Multiple Consolidation Range pivots from Excel 97-2003, so they were locked in a cave starting in Excel 2007. But you can still pull them out when you need them! If your goal is to create a pivot table, you can consolidate multiple ranges into the table. Use Alt+D P to start the pivot table Choose Multiple Consolidation Ranges Page fields are optional