Tom’s Tutorials For Excel: Importing an Access database table into Excel — a Quick Shortcut
Here’s a shortcut to manually import an Access database table into your spreadsheet. It’ll come with the alternating shaded rows and field header drop-down arrows, but it’s fast and easy. The first picture shows an access table just for example,
…
You can show your column headers as letters or numbers. Here’s how to change column headers depending on your preferred look.
…
Introduced in Excel 2016, you can click into the “Tell me what you want to do” field as shown in the picture, or press Alt+Q from your worksheet, to enter a topic for information. This is a fairly intuitive new tool, while saving the interim step of hitting the F1 key for help about a topic.
…
Tom’s Tutorials For Excel: Customizing the Status Bar
You can right-click the status bar for additional edit, formatting, and analysis options.
The following picture shows a Before and After comparison of the additional information you can choose for the status bar to show. In this example,
…
Tom’s Tutorials For Excel: Using a Ribbon Group’s Dialog Launcher
To see all the options for a Ribbon group, click the dialog launcher in the group’s bottom right corner.
…
Tom’s Tutorials For Excel: Displaying a Column’s Width
You can display a column’s width manually, or in a cell with a User-Defined Function. The first picture shows that when you press and hold your left mouse button between column headers, a column’s width appears in a tooltip.
…
Tom’s Tutorials For Excel: Using the Home key with Scroll Lock
Here are a few navigation tips using the Home key, with or without the Ctrl key, with or without Scroll Lock activated, and with Freeze Panes on or off.
…
Tom’s Tutorials For Excel: Using Undo For Several Actions at Once
You can Undo up to 100 previous actions on your workbook at the same time. Clicking the drop-down arrow next to the Undo button shows the list of your most recent actions, where you can select the point at which to undo the action(s) you want to reverse.
…
Tom’s Tutorials For Excel: Bypassing the Start screen when opening Excel
When you open Excel, here is how to bypass the Start screen when you prefer to see a new workbook instead.
…
Tom’s Tutorials For Excel: Opening Excel in a New Instance
In Excel versions 2013 and later, new workbooks open in the same instance of Excel. Here are 2 options to open your workbook in its own instance.
Option 1
Option 2
…
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: 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: 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: 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,
…
Tom’s Tutorials For Excel: “Did You Know…”
Did you know:
You can insert a new worksheet by pressing Shift+F11. Immediately thereafter, to continue inserting as many new worksheets as you want, press the F4 key.
Did you know:
You can enter any value in a cell,
…
Tom’s Tutorials For Excel: Converting a trailing negative sign to a real negative number
As any Excel developer or workplace guru knows, raw downloads from external storage warehouses can throw some strange data at you. An example is a list of numbers, some of which are meant to be negative but are actually text values because of a trailing negative sign,
…
Tom’s Tutorials For Excel: Using AutoCorrect for Faster Data Entry
Here’s a tip for shortening the effort of entering recurring lengthy text.
Suppose there are words or phrases you need to type often in your spreadsheets, such as boilerplate legal text, or the name of your company.
…
Tom’s Tutorials For Excel: Making Changes on All Sheets at Once
Here’s a time-saving tip for newcomers or casual users of Excel. When you need to make the same changes on all your worksheets, you only need to make the changes one time and still have every worksheet show those changes.
…
Tom’s Tutorials For Excel: Careful! That was *Alt*+F11 !
From your active workbook, a fast and easy way to get into the Visual Basic Editor is to press Alt+F11 on your keyboard. You can do this from any worksheet.
Careful, that was *Alt*+F11! The Ctrl key is commonly used in conjunction with other keys for keyboard shortcuts.
…
Tom’s Tutorials For Excel: Entering International Currency Symbols
While it’s true that you can insert currency symbols from the Symbol dialog box into your cell’s text, it’s faster and easier to produce those symbols by using the keyboard as you are typing your text.
Here are four examples to show how you can use the keyboard to enter currency symbols in your non-numeric text entries.
…
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: 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: 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: 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: 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: 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: 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: 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: 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: Doing Math Without Formulas or Programming
You can copy a helper cell and perform a mathematical operation in the Paste Special dialog box to work with numbers in Excel. There are no formulas or programming code involved — just a few simple mouse clicks.
…
Tom’s Tutorials For Excel: Showing a Progress Bar From a Worksheet Formula
In yesterday’s blog entry, I showed an example of using the Wingdings2 font style with Conditional Formatting to show a checkmark in a cell for completed items. Here’s how you can employ a progress bar using a the REPT function with the Wingdings font style.
…
Tom’s Video Tutorials For Excel: Searching a Cell’s Value With Alt and a Mouse Click
Here’s a video where I show how you can turn a cell’s contents into keywords for an online search, by pressing the Alt key while clicking your mouse on that cell.
…
Tom’s Tutorials For Excel: Adding Text With a Custom Format
There are times when you want to append text to a cell’s existing text, without using a helper column with a concatenation formula, and without any programming methods.
As you know, formatting a cell only changes the visual appearance of the cell;
…
Tom’s Tutorials For Excel: Toggling the GETPIVOTDATA Function On and Off
When you are doing calculations in cells outside of a pivot table that involve cells inside of (that is, belonging to) a pivot table, sometimes you want just the pivot table’s cell value, and other times you want the relative position and content of the cell with the GETPIVOTDATA function.
…
Tom’s Tips For Excel Videos: Filling Empty Cells With Value From Above
Sometimes you get data in a column which has a lot of blank cells that represent whatever text was in the last filled cell above them.
When you need to fill in hundreds or thousands of empty cells,
…
Tom’s Tutorials For Excel: Quickly Summing a Table of Numbers by Rows and Columns
Here are a couple of shortcuts to quickly sum the totals for rows and columns in a data table.
The first pair of pictures shows what you have and what you want.
…
Tom’s Tutorials For Excel: Selecting Functions From the SUM icon’s Drop-Down List
As the pictures show, you can click the small drop-down arrow to the immediate right of the SUM icon for a list of other functions to select. As the “Version 2003” picture shows, clicking the More Functions item shows the Insert Function dialog box.
…
Tom’s Tutorials For Excel: Emptying the Recycle Bin
This macro with API empties the Recycle Bin.
In a new fresh standard module:
Declare Function EmptyRecycleBin _
Lib “shell32.dll” Alias “SHEmptyRecycleBinA” _
(ByVal hwnd As Long, _
ByVal pszRootPath As String,…
Tom’s Tutorials For Excel: Creating a Right Click Event For ActiveX Controls
Free and safe downloadable workbook example.
There is no built-in menu for the right-click event of ActiveX objects. Here is a utility for that, using a CommandButton as an example.
…
Tom’s Tutorials For Excel: Updating Links (or Not)
You’ve no doubt seen this pop-up message upon opening a workbook:
To automatically update links sans VBA, if the work being done is always on your computer…
…In versions 2003 or before: Tools >
…
Tom’s Tutorials For Excel: VBA Help — Usually Right, Sometimes Wrong
The above picture shows a simple macro that calls a Message Box to display a tab name by referring to the active sheet’s CodeName property. Suppose you’ve never before heard the term CodeName,
…
Tom’s Tutorials For Excel: Finding and Deleting Phantom Links
If you’ve ever wondered why this message pops up after you are sure you’ve deleted all your workbook’s links, well, join the club. It’s a common problem because some links can be difficult to find.
In my 20 years working with Excel,
…
Tom’s Tutorials For Excel: Enter Zip Codes and Social Security Numbers With Leading Zeros
When you enter numbers that start with a leading zero such as Social Security Numbers or United States zip codes, you must stop Excel from automatically hiding the leading zero, so your full number including the leading zero is displayed.
…
San Francisco, California
1-925-310-4170
tom@atlaspm.com
Tom’s Tutorials For Excel: Showing Excel Help While Entering a Function
Tom’s Tutorials For Excel: Showing Excel Help While Entering a Function
Here’s a tip for showing Excel’s Help window for the function you are in the process of entering.
In the picture, the numbers in column C are wanting to be subtotaled. The SUBTOTAL function has 11 different mathematical argument numbers —
…
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, Tips and Tricks, Tom Urtis