Harsinchh asks if there is a way to highlight all formula cells and the precedents of those formulas. There are a lot of techniques discussed in this video:
Show Formulas in Excel
Select all Formulas in Excel
Go To Special Precedents in Excel
Direct versus All Precedents in Excel
VBA to select formulas in Excel
VBA to select precedents in Excel
VBA to union two ranges in Excel

Table of Contents
(0:00) Excel: Select Formulas & the precedents
(0:25) Excel Show Formulas
(0:55) Selecting all Excel Formulas
(1:16) Selecting Formula Precedents with Go To Special
(1:40) All Precedents versus Direct Precedents
(2:52) Four-step is a kludge
(3:08) VBA Macro to select formulas and precedents
(4:49) Personal Continue reading “Excel Select All Formulas And Their Precedents – 2380” →

Rich needs to VLOOKUP into data with ” Total” after the tracking number.

When he tries to Replace ” Total” with “”, Excel converts the tracking numbers to scientific notation. When he tries to convert everything to numbers and I lose the last several digits when they change to 0000.

In today’s video, a new option in Microsoft 365 beta to prevent the conversion to scientific notation. And then a way to hack the VLOOKUP formula.

Table of Contents
(0:00) Copy subtotals in Excel
(0:36) Replace in Excel converts to Scientific Notation
(0:54) Long numbers in Excel last digits change to zero
(1:09) Beta option Continue reading “Excel Replace Is Breaking VLOOKUP – 2379” →

Today, a great idea from Yure in Brazil.
He is using concatenation inside the HYPERLINK function to create a MailTo hyperlink. Click the cell in Excel and your e-mail program opens with the Recipient, Subject, and Body already filled in.
However… it the hyperlink grows to more than 255 characters, we are getting a #VALUE! error instead.
Can you go and vote to ask the Excel team to increase the limit for the HYPERLINK function: https://feedbackportal.microsoft.com/feedback/idea/b71ba7be-0944-ec11-a81a-0022484e21f1

In the meantime, you can use VBA code from Ron De Bruin to send e-mails from Excel. See his code at: rel=”nofollow”>https://www.rondebruin.nl/win/section1.htm

Who remembers the time that Kelly Rowland used Excel to SMS Nelly? Here is a link to that music video. https://www.youtube.com/watch?v=8WYHDfJDPDc

Table of Contents
(0:00) Sending e-mail from Excel using HYPERLINK
(1:19) Hyperlink fails with more than 255 characters
(1:52) Ron De Bruin’s VBA pages for emailing from Excel

What if you want to map custom regions in Excel 3D Map?
After yesterday’s video importing KML files from the Census.Gov site, today, we take a look at creating your own KML file using Google Maps and then exporting it to Excel.

In today’s example, I create a custom map of 10 neighborhood allotments.

Google Maps makes it easy to draw new polygons, name them, and then export the layer to KML. You can then import to Excel for Windows using 3D Maps.

Table of Contents
(0:00) Create custom shapes for Excel 3D Maps
(0:22) Neighborhood Allotments
(1:04) Open Google Maps
(1:34) Drawing a polygon for one jurisdiction Continue reading “Creating Custom KML Regions For Excel 3D Map – 2377” →

Ozveri is wondering why the Excel Filled Map Chart shows the entire world when he has just a few countries around the Mediterranean Sea. It is an annoying part of Excel Filled Map Charts. But there is a solution. It is buried where you will never find it.
Format the data series in Excel and choose Only Regions with Data.
Also in this video, I show you how to create a tiny macro in your personal macro workbook that will quickly change this setting.

The macro from 3:00 minute mark:
Sub ShowOnlyRegionsWithData()
‘ ShowOnlyRegionsWithData Macro
ActiveChart.FullSeriesCollection(1).GeoMappingLevel = xlGeoMappingLevelDataOnly
ActiveChart.FullSeriesCollection(1).RegionLabelOption Continue reading “Excel Map Chart – Only Regions With Data And Macro – 2375” →

Carl Pepperseed, on assignment in the Burmese Jungle, reports that his =IMAGE() function in Excel suddenly started returning a #CONNECT! error. While we all feel bad for Carl, maybe that means that IMAGE is now Dynamic? If we could just figure out what Carl did to bring that #CONNECT! error!
How about F9? No.
How about Ctrl+Alt+F9? No.
How about F2 / Enter? No.
Replace the URL and then Undo? The image is still there.
Copy the sheet within the workbook? No.
How about Copy the Sheet to a new Workbook? That seems to be it.
Which is bad news for the people hoping for Dynamic Continue reading “Excel Why IMAGE Function Changes To CONNECT Error” →