Tom’s Tutorials For Excel: Averaging Positive and Negative Numbers With Multiple Criteria
When crunching numbers, there are many ways to slice and dice the Average onion, depending on what criteria you want to include, exclude, combine, or isolate.
The picture shows a list of positive and negative numbers,
…
Tom’s Tutorials For Excel: Rounding Numbers By Fractions or Decimals
When rounding numbers to a particular decimal factor, you can express that rounding factor in your formula as either a fraction or as its decimal equivalent. In the pictures, the fraction one-eighth can be stated in a formula as 1/8 or by its decimal equivalent of .125.
…
Tom’s Tutorials For Excel: Evaluating Numbers as Being Whole or Decimal
There are times when you want to identify a number as being a whole number (such as 47) or a decimalized number (such as 23.5).
The picture shows three ways to apply this idea.
…
Tom’s Tutorials For Excel: Using VLOOKUP With MIN, MAX, and AVERAGE
You can nest a function as the lookup_value argument with VLOOKUP, to return an item relating to the lookup_value function. In the pictured example, MIN, MAX, and AVERAGE are nested to return the name of the salesperson associated with those functions.
…
Tom’s Tutorials For Excel: Doing Date Math on Text (non real) Dates
In yesterday’s tutorial, I showed how to place a date and time on separate lines in the same cell.
That example involved a formula with the TEXT function,
…
Tom’s Tutorials For Excel: Putting a Date on One Line and Time on Another Line in the Same Cell
Here’s a tip to place the date and time in the same cell, in separate lines.
The first step is to enter the formula
=TEXT(NOW(),”MMMM D,
…
Tom’s Tutorials For Excel: Finding the Position of the First Integer in an Alphanumeric String
When you are faced with alphanumeric strings, such as those esoteric-looking serial numbers that represent a store’s stock items, here is how you can deal with parsing them based on the position of their first integer.
…
Tom’s Tutorials For Excel: Finding the Number Closest to Zero
Here are two formulas, one to tell you the number closest to zero in a list, and the other to tell you the address of the cell holding that number. When you know a list does not contain a zero (if it did,
…
Tom’s Tutorials For Excel: Returning a Value From Every Nth Cell
Here’s a formula to help you list (that is, return) the values from every (in this case) 6 cells. This is a useful method when your data is structured such that you know the incremental factor of rows that are in between cells that carry the data you want to separately list.
…
Tom’s Tutorials For Excel: Adding and Subtracting Time in Hours Minutes and Seconds
Formula examples for hours, minutes, and seconds being added or subtracted from time.
Hours
Example to add 3 hours: =$B$1+TIME(3,0,0)
Example to subtract 3 hours: =$B$1-TIME(3,0,0)
Minutes
Example to add 16 minutes: =$B$1+TIME(0,16,0)
Example to subtract 16 minutes: =$B$1-TIME(0,16,0)
Seconds
Example to add 48 seconds: =$B$1+TIME(0,0,48)
Example to subtract 48 seconds: =$B$1-TIME(0,0,48)
Combination of Hours,
…
Tom’s Tutorials For Excel: Validating an Entry as a Real Date
One way to verify that a bona fide date is being entered into a cell is to use Data Validation.
In the pictured example, dates are being entered into a list in column E.
…
Tom’s Tutorials For Excel: Conditionally Format Five Highest or Lowest Numbers in a List
Here’s how you can utilize Conditional Formatting to identify the highest or lowest numbers in a list. Despite the literal title of this lesson, you can highlight the highest or lowest 3,
…
Tom’s Tutorials For Excel: Transposing a Dynamic List From Horizontal to Vertical
I previously posted this example, of transposing a range by copying it, and selecting the Transpose method in the Paste Special dialog box.
There are plenty of projects that require an immediate transposition using a formula to avoid the burden of manual Copy and Paste Special for Transpose every time a header cell changes.
…
Tom’s Tutorials For Excel: Counting Words in a Sentence or String
Here is a formula to count the words in a sentence or string of text.
=IF(LEN(A2)=0,0,LEN(A2)-LEN(SUBSTITUTE(A2, ” “, “”))+1)
The SUBSTITUTE function handles the possibility of the cell containing no text or value.
…
Tom’s Tutorials For Excel: Extracting Text to Left of the Second Space (or Specified Character) in a String
Previously, I posted this example of extracting text to the left of the first space, or of some specified character.
When you need the first two words in a string,
…
Tom’s Tutorials For Excel: Formatting 0 to Look Like “Zero”
There are times when you want to see a number as text in order to visually set it apart from other numbers, while maintaining its value as a number. A custom format can accomplish this, because formatting a cell’s value only affects what you see,
…
Tom’s Tutorials For Excel: Toggling to Show or Hide Your Group and Outline Buttons
When you have a worksheet with rows and/or columns that have been grouped…
…you can press your keyboard’s Ctrl+8 keys…
…to quickly and easily toggle to show or hide your Group and Outline buttons.
…
Tom’s Tutorials For Excel: Copying Your Page Setup to Multiple Worksheets
Establishing your Page Setup preferences can involve a lot of steps. You won’t want to repeat those same steps over and over for each worksheet where you’ll want the same preferences.
To show the Page Setup dialog box…
…
Tom’s Tutorials For Excel: Evaluating a Number Within an Absolute Value Range of Another Number
You will sometimes be faced with a long list of numbers, maybe thousands of rows deep, and you’ll just want to know if the sum total is within a plus or minus range of a benchmark number.
…
Tom’s Tutorials For Excel: Conditionally Formatting Locked and Unlocked Cells
There are times when your users will find it easier to enter data directly into worksheet cells, instead of a userform interface. You’ll want to protect all the cells containing formulas and static header labels, while allowing certain cells to be unprotected for users to input data.
…
Tom’s Tutorials For Excel: Finding the Absolute Row Number of an Item in a List
In yesterday’s tutorial, I posted this example to return the relative row number of an item in a list.
Today’s example offers a formula to return the actual row number of an item in a given range,
…
Tom’s Tutorials For Excel: Finding the Relative Position of an Item in a List or Table
You’ll sometimes need to know the relative position, such as the relative row in a list or table of an item. This is usually different than the item’s actual row on the Excel spreadsheet grid.
…
Tom’s Tutorials For Excel: Finding the Lowest Positive Number
Here’s how to return the lowest positive number in a list that has positive and negative numbers.
In the pictured example, the number 1 is returned in cell L3 because it happens to be the lowest number above par (in column C) in the list of this year’s Masters Golf Tournament final scores.
…
Tom’s Tutorials For Excel: Finding the Number Farthest From Zero
Sometimes you’ll need to find a number in a list that is farthest from zero, where some numbers might be positive and others might be negative.
This task has 2 considerations:
1. You will need more than a simple MIN or MAX function.
…
Tom’s Tutorials For Excel: Modifying Your List of Recently Viewed Files
You may know that the keyboard shortcut Alt+F reveals your list of most recently used files.
You can press Alt+T+O to show the dialog box to modify your list of viewable files.
…
Tom’s Tutorials For Excel: Coloring Your Worksheet Tabs
Here’s a tip for newcomers to Excel, for the ability to color your worksheet tabs. This feature has been available starting with Excel version 2002.
Right-click the worksheet tab you want to color, and select Tab Color from the pop-up menu.
…
Tom’s Tutorials For Excel: Using a Formula to Get Your Active Worksheet’s Name, and Active Workbook’s Path and Name
Here are two formulas, one to return the active worksheet’s name, and the other to return the active workbook’s full path and name. In each case, please be sure to save the workbook at least once.
…
Tom’s Tutorials For Excel: Seeing Values and Formulas on the Same Spreadsheet at the Same Time
Did you ever want to watch your spreadsheet in two separate windows in real time, where in one window you can see its values, and in the other window you can see its formulas?
…
Tom’s Tutorials For Excel: Zooming In and Out With Your Mouse Wheel
You can press the Ctrl key on your keyboard while turning your mouse wheel forward to zoom in, or backward to zoom out.
…
Tom’s Tutorials For Excel: Using Data Validation to Disallow Entry of Item in a List
Here’s an example of using Data Validation to NOT allow a particular data item entry.
Suppose you want to insure that anything can be entered into a cell, EXCEPT for certain items you specify.
…
Tom’s Tutorials For Excel: Using Data Validation to Force a Decimalized Numeric Entry
Data Validation is an excellent way to control data entry to meet a certain condition.
Suppose you want to insure that numbers entered in the yellow cells are OK for decimals,
…
Tom’s Tutorials For Excel: Going To the Precedent Cell with a Keyboard Shortcut
Here’s a cool tip for the keyboard shortcut Ctrl+[ which takes you from the formula cell you are on, to the precedent cell (if there is one) of that formula.
In this first example,
…
Tom’s Tutorials For Excel: Doing a Lookup for Last Number or Last Text in a List
Here is how you can look up items in one column, based on the last cell in a different column of that list which contains a number or text.
The formula in cell E2 is =INDEX(A3:C17,MATCH(9.99999999999999E+307,A3:A17,1),3).
…
Tom’s Tutorials For Excel: Reverse Lookup of nth Highest and nth Lowest Numbers
Here are several examples rolled into one screen shot that show how to:
• Return the minimum and maximum numbers in a list.
• Return the 2nd, 3rd, and nth highest and lowest numbers in a list.
…
Tom’s Tutorials For Excel: Copying Formulas While Keeping Their Relative and Absolute References
Here’s how you can copy a set of formulas and paste them elsewhere, while keeping the original formulas unaffected and keeping the relative and absolute references unchanged.
Before the copy and paste.
…
Tom’s Tutorials For Excel: Pasting a Formulas Static Value in Cell Below
You probably know that if you select a cell below a cell that contains a value or formula, when you press Ctrl+’ (the Ctrl and apostrophe keys), you can replicate that value or formula.
…
Tom’s Tutorials For Excel: Entering a Function’s Arguments Tooltip in a Cell
You can press Ctrl+Shift+A in mid-entry of your worksheet functions to show their tooltips’ argument text directly in your cell, and type your function arguments right over those text tips.
Step 1
Start by entering the function name,
…
Tom’s Tutorials For Excel: Looking Up the Address of an Item in a List
Here is a formula that returns the address of the cell in a list that contains a particular item. In the picture, cell D2 contains a Widget Stock Number, and cell E2 contains this array formula to return the item’s address:
=ADDRESS(MIN(IF(A3:A15=D2,ROW(3:15))),1)
Recall,
…
Tom’s Tutorials For Excel: Dynamic Summing From the Active Cell
Here’s a cool formula that you can plug into any cell, which will dynamically sum a list of numbers from the top of the list to the cell of the row the formula is in.
For example,
…
Tom’s Tutorials For Excel: Listing Column Letters Across and Down
Here are formulas to display the column letter in any individual cell, or to list column letters horizontally across a row, or vertically down a column.
As shown in this first picture, you can display any cell’s column letter with the formula
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),”1″,””)
You can use that same formula,
…
Tom’s Tutorials For Excel: Finding the Minimum and Maximum Numbers in a Filtered List
You can use the SUBTOTAL function to look up the minimum or maximum number in a filtered list.
In the picture, the formula in cell B1 that returns Sue Flay’s minimum sales number is
=SUBTOTAL(5,B5:B100)
The formula in cell B2 that returns Sue Flay’s maximum sales number is
=SUBTOTAL(4,B5:B100)
The first argument for SUBTOTAL is Function_Num,
…
Tom’s Tutorials For Excel: Summing Only Positive or Negative Numbers
In a list that contains positive and negative numbers, here are formulas that can sum those numbers in different ways, depending on the nature of your project.
Based on the picture:
• Formula of only positive numbers summed: =SUMIF(B3:B15,”0″)
• Formula of only negative numbers summed: =SUMIF(B3:B15,”
…
Tom’s Tutorials For Excel: Converting an Elapsed Time to a Decimal Number
Some employers pay their employees based on work time that is measured in decimals as portions of an hour. For example, if an employee works a 7-hour and 45-minute day, the employer pays that person for 7.75 hours of work time.
…
Tom’s Tutorials For Excel: Finding the First and Last Days of the Week and Month
Here are formulas to return various dates of first and last days of a given month.
First day’s date of that month: =DATE(YEAR(B1),MONTH(B1),1)
Last day’s date of that month: =DATE(YEAR(B1),MONTH(B1)+1,0)
First Monday date of that month:
=DATE(YEAR(B1),MONTH(B1),8)-WEEKDAY(DATE(YEAR(B1),MONTH(B1),6))
Last Friday date of that month:
=DATE(YEAR(B1),MONTH(B1)+1,1)-WEEKDAY(DATE(YEAR(B1),MONTH(B1)+1,1)-6)
First day of the month,
…
Tom’s Tutorials For Excel: Calculating Dates for Last Day of Current, Previous, and Future Months
As pictured below, here are formulas that return the date for the last day of…
• Current month: =DATE(YEAR(NOW()),MONTH(NOW())+1,0)
• Last month: =DATE(YEAR(NOW()),MONTH(NOW()), 0)
• Next month: =DATE(YEAR(NOW()),MONTH(NOW())+2,0)
…
Tom’s Tutorials For Excel: Summing a Range Diagonally
Every now and then you come across an unusual request to do this or that in Excel. Such an example is summing a range of numbers diagonally, as shown in the picture for B11:F15.
The array formula that accomplishes this in cell B17 is
=SUM(B11:F15*(ROW(B11:F15)=COLUMN(B11:F15)+9))
Recall,
…
Tom’s Tutorials For Excel: Formatting Large Numbers as Decimalized Gigabytes
When you work with numbers so large that Excel puts them in Scientific Notation format, you might want to format those numbers for a more meaningful look.
For example, in the Before and After comparison pictures,
…
Tom’s Tutorials For Excel: Conditionally Formatting a Specific Weekday Date
In Excel, the 7 days of the calendar week can be identified by their index number using the WEEKDAY function, starting from 1 (Sunday) to 7 (Saturday). For example, you can use Conditional Formatting to highlight cells with dates that fall on a Friday when the WEEKDAY function returns the number 6.
…
Tom’s Tutorials For Excel: Entering an Ampersand in Your Header or Footer
If you’ve ever wondered why a custom header or footer does not show an ampersand (&) when you want to show it, there’s a small trick to making that happen. All it takes is two ampersands in succession,
…
San Francisco, California
1-925-310-4170
tom@atlaspm.com
Tom’s Tutorials For Excel: Selecting All Cells With Comments or Data Validation
Tom’s Tutorials For Excel: Selecting All Cells With Comments or Data Validation
I previously posted this example of selecting only constants or formulas.
You can do the same with cells that contain comments or data validation.
Select the range of interest.
…
Read more ›
Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Comments / Screen Tips, Data Science, Data validation, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis