Category: Excel

Excel Formula to Add 12,12,12,12 from a cell to get 48!?!? Excel Magic Trick 1817

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1817.xlsm

Learn about how take CSV Number Data like 12, 12, 12, 12 and add to get 48. See a Microsoft 365 formula and then an Excel Formula that will work in any version of Excel!
Topics:
1. (00:00) Introduction
2. (00:04) Goal of Video
3. (00:26) Microsoft 365 Formula: TEXTSPLIT and SUM
4. (01:23) Formula that works in any version: SUBSITUTUE function
5. (02:12) Excel 4.0 Macro Function and Macro Sheet
6. (03:09) EVALUATE function
7. (04:11) Relative Cell Reference in a Defined Name
8. (05:08) Closing, Video Links

#excelisfun #excel #excelformulas #freeclass #excelfunctions #addtext #freeclasses #freecourse Continue reading “Excel Formula to Add 12,12,12,12 from a cell to get 48!?!? Excel Magic Trick 1817” →

Using Excel And HYPERLINK To Automate Custom E-Mail – 2378

Posted on by Annie D

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

Excel 3D Map With KML Custom Regions – 2376

Posted on by Annie D

Mapping Census Tracts in Excel.
Excel supports mapping by county, zip code, state, country. But they don’t natively support census tracts. In today’s video, you will see how to download population data and a map KML file from Census.Gov.

Learn how to import the KML file into the 3D Map feature in Excel and make a map from it.

This video also covers:
Downloading a CSV with population by census tract from Data.Census.Gov.
Downloading a KML file by Census Tract
Importing and merging that data in Excel.

Table of Contents
(0:00) Excel Mapping Other Jurisdictions
(0:51) Four Video Sections
(1:19) Examining KML File in Notepad++
(2:14) Opening 3D Map
(2:54) Importing KML Continue reading “Excel 3D Map With KML Custom Regions – 2376” →

Creating Custom KML Regions For Excel 3D Map – 2377

Posted on by Annie D

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” →

Help Delete FAKE excelisfun Account

Posted on by Annie D

Go to this fake channel: https://www.youtube.com/@ExcelIsFun_./about
and report channel as impersonating excelisfun to illegally spread ads. The real account is (they need it in report): https://www.youtube.com/@excelisfun

#fake #abuse #fakeyoutubers #youtube #youtubebots #report #deleteyoutube #bots #illegal

Excel Map Chart – Only Regions With Data And Macro – 2375

Posted on by Annie D

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” →

Excel Calculate The Selection – 2374

Posted on by Annie D

In Excel, can you calculate just the selected cells?

Jennifer has a huge spreadsheet that takes forever to calculate. After calculating the entire workbook, the totals on SheetA have not updated. This short VBA macro will allow her to calculate only the selected range.

Table of Contents
(0:00) Problem: Entire workbook does not recalc
(0:55) Add Manual & Automatic Calculation to QAT
(1:12) Explanation of Manual Calculation Mode
(1:56) Open VBA Editor add use Selection.Calculate
(2:35) Saving Personal.XSLB
(2:47) Adding Macro to QAT in Excel
(3:20) Calculating selection in Excel
(3:50) Broken Workbooks
(5:00) Wrap up with Wally & Nancy

Excel Why IMAGE Function Changes To CONNECT Error

Posted on by Annie D

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” →

Excel Create A Personal Macro Workbook – 2373

Posted on by Annie D

How to Create a Personal Macro Workbook in Excel.

I have a lot of other videos where I want you to put a few lines of VBA into your Personal Macro Workbook. I am always thinking that many people won’t have a Personal Macro Workbook. My goal with this video is that any time a future video mentions the Personal Macro Workbook, I can ask you to watch this video to create a Personal Macro Workbook and then you can go back to the other video for the VBA code for that particular task.

Table of Contents
(0:00) Excel Create a Personal Macro Continue reading “Excel Create A Personal Macro Workbook – 2373” →

Excel Sum The Comma Delimited Numbers From One Cell – 2372b

Posted on by Annie D

Om asks: I have 123,413,413,1352,244­ in a single cell.
There are many numbers with commas in between in each cell.
How to Sum all the numbers using a formula?
I don’t want to use Text to Column.
By the way, it is Excel 2010.

I use Brad Yundt’s three-line VBA function to split the numbers.
Then Excel formulas to sum them.
Also, an easier formula if you have Microsoft 365.

Table of Contents
(0:00) Sum all comma-delimited numbers in cell using an Excel formula
(0:19) Easy solution with TEXTSPLIT
(0:32) Switch to VBA for Brad Yundt Splitter code
(0:55) Using SPLITTER custom function in Excel
(1:25) Wrap up with Wally & Nancy