Category: Excel

Auto Numbers in an Excel Table with a Formula. #Short Excel Magic Trick 08

Posted on by Annie D

Learn how to create an auto number for records in an Excel Table using a formula.
Download Excel File: https://excelisfun.net/files/SEMT0008-12.xlsx
#excel #excelisfun #analytics #microsoftexcel #microsoftmvp, #short #shorts #excelmvp #excelformulas #excelfunctions #excelformulaforjobinterview #excelforbegginers #excelshorts #exceltutorial #exceltips #exceltipstricks #freeclass #freecourse #freeclasses #freeexcelcourse #freeexcel
#exceltable #sequencefunction #sequence #increment #indexfunction #structured #nomenclature

Excel Challenge: Most Efficient Way To REPTARRAY – 2585

Posted on by Annie D

Dave wishes that Excel would offer a REPTARRAY function, just like REPT but for arrays. This video shows four different ways to repeat an array N times.

Vote for Microsoft to add REPTARRAY:
https://feedbackportal.microsoft.com/feedback/idea/0f6d6e22-7cb3-ed11-a81b-002248519701

Details about FastExcel: http://mrx.cl/speedv4

Here are the formulas used in the video:
=TEXTSPLIT(REPT(TEXTJOIN(“,”,TRUE,A)&”,”,$E$1),,”,”,TRUE)
=DROP(REDUCE(“”,SEQUENCE($E$1),LAMBDA(a,b,VSTACK(a,D))),1)
=LET(a,E,rw,COUNTA(a),MAKEARRAY(rw*$E$1,1,LAMBDA(r,c,CHOOSEROWS(a,MOD(r-1,rw)+1))))
If you have FastExcel V4 from Charles Williams: =REPEAT(A2#,E1)

If you have a better way, please post below in the YouTube comments.

Excel Most Efficient Way to REPTARRAY Challenge
(0:00) How to REPTARRAY
(0:51) JOIN, REPT, then SPLIT
(1:52) REDUCE, SEQUENCE, LAMBDA, VSTACK
(2:51) MAKEARRAY, LAMBDA, CHOOSEROWS, MOD
(3:56) FAST EXCEL V4
(4:56) Vote for REPTARRAY
(5:42) Nancy Faust

Referencing an Excel Sheet With An Apostrophe in its Name – Solved!

Posted on by Bb

The Problem – Apostrophes in Sheet Names

You are trying to use a formula to reference an Excel Sheet with an apostrophe in its name and you keep receiving an error message or a #REF! reference error.

The Solution (Very Easy!)

Great news! Simply replace the apostrophe with two apostrophes in your formula. That’s right, two consecutive apostrophes, not a quote. Very simple solution indeed!

Below I provide two examples. You can manually update the name in your formula (or in the cell you are referencing if using an indirect lookup), or if working with many sheet names, you may opt to use the substitute solution shown below (formula displayed in E5).

The substitute can be nested in an indirect formula also.

Excel Sheet with an apostrophe solution
Excel Sheet With An Apostrophe Solution

Please comment below on any other challenges you would like for me to cover. I am contemplating doing a quick solution series in this format!

Also, be sure to check out Dose for Excel (click the image below)! Add over 100 functions to Excel to increase your productivity and more!  Disclosure: this is an affiliate link, so I may earn a small commission if you decide to purchase the add-in. Thanks for supporting my blog!

Dose for Excel - +100 Functions

Some topics addressed in this post:
Ref Error in lookup
#REF! Error in lookup
Ref Error in formula
#REF! Error in formula
How to fix ref error in Excel from apostrophe
Apostrophe in Excel sheet name issue



How To Multiply In Excel 2584

Posted on by Annie D

How to Multiply in Excel. This video covers eight examples:
Multiply Symbol is * not X.
Multiply Column by Number.
Multiply by Percent.
Multiply two Columns and Copy Down.
Multiply by Same One Cell Reference.
Multiply 3 Cells.
Multiply Many Cells.
Two Columns and Sum.

Table of Contents
(0:00) Intro
(0:08) Multiply Symbol is * not X.
(0:12) Multiply Column by Number.
(0:24) Multiply by Percent.
(0:32) Multiply two Columns and Copy Down.
(0:42) Multiply by Same One Cell Reference.
(0:57) Multiply 3 Cells.
(1:06) Multiply Many Cells.
(1:17) Two Columns and Sum.

This video answers all of these common search terms:
What Symbol Is Multiply In Excel?
How To Type Multiply Symbol In Excel?
How To Multiply 1 Cell In Excel Continue reading “How To Multiply In Excel 2584” →

Convert ISO Dates to Dates with Power Query: Excel or Power BI. #Short Excel Magic Trick 07

Posted on by Annie D

Using Power Query, learn how to convert ISO Dates into Dates.
#excel #excelisfun #analytics #microsoftexcel #microsoftmvp, #short #shorts #excelmvp #excelformulas #excelfunctions #excelformulaforjobinterview #excelforbegginers #excelshorts #exceltutorial #exceltips #exceltipstricks #freeclass #freecourse #freeclasses #freeexcelcourse #freeexcel #powerquery #powerbi #powerbidesktop #powerbishorts

Excel Copy VBA Module to Different Workbook

Posted on by Annie D

Microsoft Excel – Copying VBA Module from one workbook to another.
John B has a workbook with a lot of data.
He created a new workbook with a macro for testing. Now that the macro is working, he wants to copy the working macro from the test workbook to the workbook with all of his data.
This video shows how to launch the VBA Editor with Alt+F11
Display the Project Explorer.
Drag the module from one workbook to another.

Remember to Save As the workbook as XLSB or XLSM. If you save the workbook as XLSX, your macros will be removed.

Topics:
How to copy Excel VBA?

Continue reading “Excel Copy VBA Module to Different Workbook” →

Remove Page Watermark From Excel – 2582

Posted on by Annie D

How to get rid of a watermark in Excel? There are three different types of watermarks that are common in Excel. This video discusses all three.
Table of Contents
(0:00) Remove Watermark from Excel
(0:12) Remove Page Watermark
(0:33) Removing Watermark from Header
(1:06) Visible Watermark that does not print

This video answers these common search terms:
excel how to get rid of watermarks
how do i remove a watermark in excel
how do you remove a watermark from excel?
how do you remove a watermark in excel
how do you remove watermark in excel
how to get rid of watermark excel
how to get rid of watermark in excel
how to get rid Continue reading “Remove Page Watermark From Excel – 2582” →

How to Get the Sum At the Top of Excel #shorts

Posted on by Annie D

How to get the sum at the top of Excel?
This short video shows two methods:
Add the sum at the bottom and then Cut using Ctrl+X and paste at the top.
Or, start at the top, click AutoSum, and then select the range of numbers with the mouse.

How To Get Sum At Top Of Excel
How To Sum At The Top In Excel

Excel Duplicates – Mark or Remove – 5 Answers in 55 Seconds – #shorts

Posted on by Annie D

5 Excel topics in 55 seconds. In this video, I answer five common questions about duplicates in Excel. This video will address:
excel find duplicates without deleting
can excel find duplicates
excel duplicates find
can i find duplicates in excel
excel remove duplicates without losing data
excel find duplicates in a list
can excel highlight duplicates
excel duplicates highlight
can excel look for duplicates
can excel pick up duplicates
excel recognize duplicates
how to see if duplicates in excel
how to see duplicates in excel column
excel check for duplicates in a list
excel duplicate check
excel check if duplicates in range
excel for duplicates
excel duplicates in one column
excel duplicates color
excel and duplicates
excel duplicates conditional formatting
excel duplicate Continue reading “Excel Duplicates – Mark or Remove – 5 Answers in 55 Seconds – #shorts” →

Why Excel SUM is Zero #Shorts

Posted on by Annie D

Why Excel SUM is zero.
Why Excel Sum function not working.
Why Excel SUM returns 0.
Why Excel Sum is wrong.
Why Excel Sum shows 0.
Why Excel Not Showing Sum.
Why Excel Sum Formula Not working.

Do you have a column of numbers that won’t sum in Excel? It is most likely that the numbers have been stored as text. Here are two quick ways to convert those text numbers and the numbers will then sum.

One frustrating behavior when this happens: =SUM(A2:A4) is zero, but =A2+A3+A4 calculates correctly. That is because the plus sign coerces a number out of the text, but the SUM function is Continue reading “Why Excel SUM is Zero #Shorts” →

Excel FILTER() With Two Conditions #shorts

Posted on by Annie D

Keith wants to use the Excel FILTER function to get all names where two conditions are true: Status is A and the Project does not start with 22-05. This video shows how to combine 2 conditions in an Excel FILTER formula.

excel filter function
why excel filter not working
excel can you filter columns
excel can you filter with LEFT
excel can you filter to two conditions