Tom’s Tutorials For Excel: Setting Alignments For Date Left and Time Right in a Single Cell
If you have the date and time in a single cell, such as when you enter the NOW function, you can custom format the cell as MMM D, YYYY * H:MM AM/PM to separate the date (flush left inside the cell),
…
Tom’s Tutorials For Excel: Selecting a Large Range Without Scrolling or Dragging.
Here is a handy shortcut tip when you need to select a large range. In the example, I selected a small range of B2:H5 so the pictures can fit onto this web page for demonstration purposes.
…
Tom’s Tutorials For Excel: Comparing Lists For Identical Match
Here’s an example with the formula =AND(A1:A10=B1:B10) that returns TRUE when all pairs of cells match in a two-column list, and FALSE when at least one pair is different in any way.
This is an array formula,
…
Tom’s Tutorials For Excel: Repeating Your Last Action FAST! The Magic of the F4 Key.
Take a look at this gem: The F4 key is a little-used but highly efficient shortcut for speeding up your Excel tasks, when you need to repeat the same action once,
…
Tom’s Tutorials For Excel: Filling Series For Linear and Growth
Here is an example of building a table of projected sales and income, using three Fill methods of Series, Linear, and Growth.
TIP:
First Example: Fill Series (add a year number to each filled cell)
• Enter the first year of your data,
…
Tom’s Tutorials For Excel: Extracting the Last Word in a Cell
When you want to extract the last word in a cell, be it a last name or whatever the word or character is that follows the last space in a string, this formula can do that,
…
Tom’s Tutorials For Excel: Programming Multiple Drawing Shapes
Here’s how you can assign one macro to multiple drawing shapes on your worksheet, and have them all behave uniquely, depending on which shape was clicked.
In the pictured example, four Oval drawing shapes were placed on the worksheet,
…
Tom’s Tutorials For Excel: Using the YEARFRAC Function For Fractional Years Between Dates
The YEARFRAC function returns a decimalized number as a fraction of year(s) that represents the count of whole days betweeen the start date (“Hire Date” in the pictured example), and the end date (today’s date in cell F1).
…
Tom’s Tutorials For Excel: Converting Formula References From Absolute to Relative
I previously posted this manual example, and this programmed example for converting formula references from relative to absolute. The manual example showed how the row and / or column references can be kept or omitted.
…
Tom’s Tutorials For Excel: Shortcuts to Select the Next, Many, or All Sheets
Here are a few shortcut tips for selecting worksheets.
Pressing the Ctrl+PageDown keys will select the next (to the right) visible sheet.
Pressing the Ctrl+PageUp keys will select the previous (to the left) visible sheet.
…
Tom’s Tutorials For Excel: Formatting Dates as Past or Future
Here’s how you can mark dates in the past and/or in the future, based on some time interval such as less than or more than 30 days from today’s date. For example, in the first picture,
…
Tom’s Tutorials For Excel: Using VLOOKUP for Reverse Lookups
I previously posted this example of a reverse lookup, using the INDEX and MATCH functions.
The VLOOKUP function is commonly used with a table of data where the item being looked up is in a column to the left of the column holding the corresponding item you want to return,
…
Tom’s Tutorials For Excel: Listing Subfolders
I previously posted this example to list files kept in a parent folder.
Today’s example shows how you can list the subfolder names belonging to a parent folder.
Here’s the macro that does this:
Sub ListSubfolders()
‘Declare and define variables.
…
Tom’s Tutorials For Excel: Returning text with your formula’s numeric results
I previously posted this example with text, and this example with dates, to show how a custom format can display text in cells without affecting the actual underlying values of those cells.
…
Tom’s Tutorials For Excel: Listing Your Excel Files From a Folder
Here’s a macro to list the names of all your Excel workbooks from a specified folder into column A of your worksheet.
In the first picture, you see several different types of files in the selected folder.
…
Tom’s Tutorials For Excel: Avoiding the #DIV/O! (Cannot Divide by Zero) Error
I previously posted this example with the SUMIF function for bypassing the #DIV/O! error.
Some errors are unavoidable, depending on the nature of your project. You probably know about the ISERROR function,
…
Tom’s Tutorials For Excel: Highlighting the Current and Prior Selected Cells.
I previously posted this example for highlighting the selected cell.
Today, here is how you can highlight not only the current cell, but the cell you’d selected before you selected your current cell.
…
Tom’s Tutorials For Excel: Toggling a formula for relative or absolute references
I previously posted this macro example for converting a bunch of formula-containing cells all at once, from relative to absolute references.
Alternatively, you can quickly toggle a single formula’s row and/or column references for relative and absolute with an easy keyboard shortcut.
…
Tom’s Tutorials For Excel: One formula returns value of the same cell on multiple worksheets
Here’s how one formula can return the value of the same cell address from multiple worksheets. In the picture, you have an expense workbook with five worksheets. Four of the worksheets (named Sheet2,
…
Tom’s Tutorials For Excel: Entering New Lines and Bullets in a Cell
Here is how you can enter text on its own line in a single cell. In the picture, some food items have been entered into cell A2. As you can see in the pictures that follow,
…
Tom’s Tutorials For Excel: Deleting Duplicate Records
When you have a list of data, sometimes it is not enough to simply delete rows with duplicated information based only on the items in one column. Multi-column lists can have duplicated records based on the fact that every item in every column of a row’s data matches that of another row’s entire data,
…
Tom’s Tutorials For Excel: Finding and Replacing Wildcards
In this previous Tom’s Tutorial blog post, I showed an example of using wildcards in formulas. Today’s Tutorial shows how to find and replace wildcard characters themselves on your worksheet.
To review, in Excel,
…
Tom’s Tutorials For Excel: Shortcutting a Cell for PasteSpecial to Values
Here’s a quick way to use your keyboard for achieving the effect of copying a cell, and pasting it special for values.
Instead of mouse clicks and menus that are commonly used,
…
Tom’s Tutorials For Excel: Converting Time to Minutes and Seconds
Here is how you can take an elapsed time originally represented in Hours, Minutes, and Seconds, and show it as just Minutes, or as Minutes and Seconds. For example, the picture shows the top 10 finishing times in column B for runners of the 2012 Boston Marathon.
…
Tom’s Tutorials For Excel: Shortcut to Activate the Next or Last Open Workbook
If you have a bunch of workbooks open in the same instance of Excel, and you want to cycle through them in turn to activate the next ones (to the right on your task bar),
…
Tom’s Tutorials For Excel: Extracting the First Word or String in a Cell
Here is an example for returning the first word in a cell, with a formula that can be easily modified to return all contents to the left of the first found specified character in a cell.
…
Tom’s Tutorials For Excel: Summing Numbers While Omitting the Lowest Two
I recently posted two examples of array constants:
how to avoid a lookup table for VLOOKUP, and how to average the 4 highest numbers in a list.
Here’s how you can sum all numbers in a list except for the two lowest or the two highest.
…
Tom’s Tutorials For Excel: Averaging the Four Highest Numbers in a List
I recently posted this example of an array constant to avoid a lookup table for VLOOKUP.
Today, an array constant is employed to collect — and then calculate the average of —
…
Tom’s Tutorials For Excel: Calculating the Date for Easter
Easter (often referred to as Easter Sunday) is a Christian festival and holiday. Easter always falls on a Sunday, but its date varies depending on the calendar year and the phase of the moon.
Specifically,
…
Tom’s Tutorials For Excel: Averaging Only Positive Numbers, Ignoring Blanks and Text
When you have a mixed list of cells that contain negative numbers, positive numbers, text, and blanks, here is a formula to calculate the average for only the positive numbers.
In the picture,
…
Tom’s Tutorials For Excel: Calculating a Month’s Fiscal Quarter Number
Most businesses account their annual operations on a January to December schedule. For those businesses, their quarterly calendar looks like this:
Quarter 1: January, February, March.
Quarter 2: April, May, June.
Quarter 3: July,
…
Tom’s Tutorials For Excel: Hiding Zeroes in Cells
Here’s a way to hide zeroes in cells, to improve the readability of your spreadsheet by making only the relevant non-zero numeric values visible.
Select the range of interest, right-click on that selection,
…
Tom’s Tutorials For Excel: Formatting a Date With Text
Here’s how you can enter a date in a cell, and apply a custom date format to make the cell look as if it contains text. You can use that date in math functions elsewhere, as shown below.
…
Tom’s Tutorials For Excel: Entering Fractions
Here’s how to enter a fractional value in a cell, make it look like a fraction in the cell, and be able to work with it mathematically as you would with any number.
…
Tom’s Tutorials For Excel: AutoFilling Dates For Weekdays Only
Here’s how you can fill in a range of dates that only includes traditional workdays of Monday through Friday, while bypassing the weekend dates for Saturdays and Sundays.
Step 1
You start by entering your beginning date into a cell.
…
Tom’s Tutorials For Excel: Formatting Cell Contents as Invisible
Here’s how you can make a cell’s contents be invisible, while keeping the cell’s rows and/or columns unhidden. In the following pair of pictures, employee salaries are in column F, but you want them hidden while column F remains visible.
…
Tom’s Tutorials For Excel: Formatting Numbers in Decimalized Millions
When you have large numbers that you want to display in millions for easier reading, you can custom-format those cells as
#.0,,;#.0,,;0
…
Tom’s Tutorials For Excel: Formatting a Negative Number In Square Brackets
Here’s a quick method for displaying a negative number in square brackets.
The custom format is 0.00;\[0.00\]
…
Tom’s Tutorials For Excel: VLOOKUP Without a Lookup Table
When using a lookup function such as VLOOKUP, you can avoid a lookup table on a worksheet and insert the lookup elements directly into the formula itself. This is done by using an array constant which is shown by example in the pictured formula
=VLOOKUP(B4,{0,”D”;71,”C-“;72,”C”;76,”C+”;79,”B-“;
…
Tom’s Tutorials For Excel: Showing Your Named Ranges At 39% Zoom
Here’s a tip to get a bird’s eye view of the named ranges on your worksheet.
All you need to do is set your worksheet’s Zoom to 39% or less.
…
Tom’s Tutorials For Excel: Flipping a List
If you want to flip a list so the order of items is vertically reversed, here’s one way to do that using this formula in the pictured example:
=INDEX(A:A,COUNTA(A:A)+1-ROW())
…
Tom’s Tutorials For Excel: Forcing Entries in UPPER Case
Here’s how you can force users to enter all alpha (letter) characters in UPPER case, using Data Validation and no programming code.
To make this happen, press Alt+D+L on your keyboard to show the Data Validation dialog box.
…
Tom’s Tutorials For Excel: Entering a Static Date and Time
As you probably know, you can enter =TODAY() and get the current date, or =NOW() and format the cell as a time to get the current time.
Those functions belong to a category of volatile functions,
…
Tom’s Tutorials For Excel: Using the Keyboard to Select a Row, Column, or All Cells
Here are a few keyboard shortcuts for selecting your worksheet cells.
To select the entire row of your active cell, press the Shift+Spacebar keys.
To select the entire column of your active cell,
…
Tom’s Tutorials For Excel: Identifying Your Active Window’s Top Left Cell
Here are a few macros that identify the top left cell of your active window’s spreadsheet.
The first example relates to this picture, where cell HX63524 happens to be the top left cell in the active window.
…
Tom’s Tutorials For Excel: Calculations Using the F9 Key
Here are four keystrokes involving the F9 key.
…
Tom’s Tutorials For Excel: Toggling to Show All Formulas or All Values
By pressing the Ctrl+~ keys on your keyboard, you can toggle between showing all the worksheet’s formulas or showing only values.
…
Tom’s Tutorials For Excel: Inserting a Word Document Onto Your Spreadsheet
Here are the simple steps for inserting a Word document directly onto your worksheet.
Step 1
From the Ribbon, click the Insert tab and then click the Object icon.
If you are using Excel’s version 2003 or before,
…
Tom’s Tutorials For Excel: Selecting Only Constants or Formulas
Sometimes you want to do something — such as format, delete or edit — all cells containing constants or formulas at the same time. Here’s how to select all such cells so you can thereafter do to them what you want.
…
San Francisco, California
1-925-310-4170
tom@atlaspm.com
Tom’s Tutorials For Excel: Putting Comments In Formulas With The N Function
Tom’s Tutorials For Excel: Putting Comments In Formulas With The N Function
You can add a text notation to a worksheet formula by using the N function. This is useful when you want to look at a formula and know something about it by reading actual descriptive words.
…
Read more ›
Tags: Analytics, Big Data, Business Intelligence, Comments / Screen Tips, Data Science, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis