Many people are asking about the AutoSum shortcut in Excel.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
MrExcel Excel Videos
Learn Excel from MrExcel - Excel tips and tricks from Bill Jelen.
This video covers six topics about conditional sums in Excel.
Table of Contents
(0:00) Topics in this video
(0:33) Conditional Sum in Excel
(1:17) SUMIFS with multiple criteria
(2:09) SUMIFS with 2 criteria in same column
(5:33) How to Drag SUMIFS formula
(6:27) SUMIFS with Function in Criteria
(7:22) SUMIFS with Range of Dates
How to calculate a percentage of total in Excel.
Common search terms answered by this video:
How to add percentages to sum in Excel
how to calculate percentage of a sum in Excel
how to change a percentage on a sum on Excel
how to do a percent sum in Excel
how to do a percentage of a total sum on Excel
how to do a percentage sum in Excel
how to do a percentage sum on Excel
how to do an percentage sum on Excel
how to do percentage sum in Excel
how to find a precentage of sums in Excel
Table of Contents
(0:00) Add a Total in Excel
(0:10) Division formula in Excel
(0:22) F4 to add dollar signs in Excel for absolute reference
(0:32) Formatting as a percentage with 1 decimal place
(0:37) Double-click Fill Handle to copy formula
(0:45) Wrap-up
Carlos asks how to have the smallest number have the largest data bar in Microsoft Excel.
Microsoft Excel Conditional Formatting offers three data visualization tools. Two of them are easily reversible. But Data Bars don't have an easy way to reverse.
Nancy Faust takes us into the "Danger Zone" as I try using a devious number format to mask negative numbers as positive numbers.
Table of Contents
(0:00) Small Number Long Data Bar
(0:14) Reversing Color Bar in Excel
(0:24) Reversing Icon Sets in Excel
(0:42) Data Bar outside cell
(1:08) Negative Values Data Bar setting
(1:24) Show Bar Only in Data Bars
(1:34) Danger Zone: Devious Number Format
(2:14) Showing Negative numbers as positive in Excel
(2:45) Wrap-up
Microsoft Excel Puzzle and Solutions.
Bob Umlas sent in the puzzle. How to refer to every 3rd column on Sheet2?
Table of Contents
(0:00) Bob Umlas Book
(0:22) Problem: Refer to every 3rd cell
(0:44) CHOOSECOLS with SEQUENCE
(1:16) Horizontal XLOOKUP
(1:46) Fill Handle from 3 Cells
(2:32) Delete Empty Cells
(2:54) R1C1 Method 2
(3:43) Replace = with x=
(4:12) Change R1C1 to A1
(4:21) Insert colums/rows to test
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
The five-number summary in Excel is Min, first quartile, median, upper quartile and Max. This video shows three ways:
Method 1: Three formulas
Method 2: LET function
Method 3: LAMBDA function
This video answers these Search terms:
does excel already have a 5 number summaryy option
how do you obtain a five number summary in excel
how do you use excel to find 5 number summary
how to create a 5 number summary in excel
how to create a five number summary in excel
how to determine five number summary excel
how to do 5 number summary in excel
how to do a 5 number summary in excel
how to do a five number summary in excel
how to do five number summary in excel
how to find 5 number summary frommexcel
how to find the 5 number summary excel
how...
How to generate a random selection in Excel with no repeats.
Choose random names from the Data Set, but do it without repeating a namek. In a revisit to Episode #1471, how will Bill assure that there are no repeats in the Random selection? Follow along with Episode #2570 today to learn the answer and solve the challenge.
This video requires new Excel functions available in Microsoft 365. If you don't yet have RANDARRAY, SORTBY, or SEQUENCE, then use the old method in video 1471:
The PowerBall lotto formula shown near the end of the video is:
=TEXTJOIN(", ",,SORT(TAKE(
SORTBY(SEQUENCE(69),RANDARRAY(69)),5)),"PB="&RANDBETWEEN(1,26))
Table of Contents
(0:00) Choose 8 names from a list of 26 names
(1:18) Choose N names from a list of...
Why is Excel not showing the SUM in the status bar at the bottom of Excel?
There are a few reasons covered in this short video.
1) You only selected a single cell
2) You've selected a range of text numbers
3) One or more error cells in the selection
4) Someone turned off the SUM
