Tom’s Tutorials For Excel: Programming the Anatomy of a Pivot Table and Pivot Chart
Here is a step by step example of how to program a pivot table and pivot chart, including how to place those objects exactly where you want them on your worksheet.
The programming code is in the downloadable workbook,
…
Tom’s Tutorials For Excel: Conditional Formatting examples for repeated items in a list.
The Conditional formatting rule for unique entries in range A3:A14 is
=COUNTIF($A$3:$A$14,A3)=1
The Conditional formatting rule for non-unique entries in range B3:B14 is
=COUNTIF($B$3:$B$14,B3)1
The Conditional formatting rule for repeated entries in range C3:C14 is
=COUNTIF($C$3:C3,C3)1
…
Tom’s Tutorials For Excel: Running a PowerPoint Presentation From Excel
Download this Excel workbook that runs a PowerPoint presentation.
Download this accompanying PowerPoint presentation.
Here is how you can run a PowerPoint presentation from Excel, with the below macro named “PowerPointSlideshow”.
…
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.
…
You can get the last saved date and time for any workbook whether it is open or closed. In Step 3, for demonstration purposes in the Immediate window, I preceded that line of code with the then-current date and time. In the following pictures, Book1 is the only workbook open in the Visual Basic Editor’s Project window.
…
I previously posted this example showing how to enter separate lines of text into a single cell. This example shows how to move each line of a cell’s text into its own cell.
The first picture shows five cells in range A3:A7 that each have three lines of text for a person’s name,
…
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.
…
Introduced in Excel 2016 is the Multi-Select icon for pivot table slicers, a welcome convenience to the heretofore requirement of pressing the Ctrl key to select multiple labels.
In the picture for example, I clicked the Multi-Select icon, then clicked to deselect the states of Arizona, Iowa, and Michigan from appearing in the pivot table’s Sum of Sales.
…
Here are two formula options to sum a range of cells, when each cell holds a delimiter (in this example, a hyphen character), and you want to separately sum the cells’ numbers to the left and right sides of the delimiter.
In the picture, the array formula in cell B14 that sums the San Francisco Giants’
…
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 Formula to Transpose a Vertical Range Horizontally
As the picture shows by example, you can horizontally transpose a vertical range at any cell outside the vertical range with the formula
=INDEX($A$2:$A$25,COLUMNS($A$2:A25))
Note the absolute and relative references.
…
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: Hiding Your Named Ranges
In the below picture, your workbook’s named ranges can be shown by clicking the down arrow next to the Name box.
The following macro hides the named ranges, and as the next picture shows, you can work with hidden named ranges the same as you would when they are visible.
…
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 SUMPRODUCT on Multiple Columns
Most examples of the SUMPRODUCT function show a single list of numbers being evaluated for a particular criteria. The following 6 pictures show a simple modification involving SUMPRODUCT, to demonstrate some versatility with that function.
The first picture shows a range of monthly sales of a few warehouse items that are listed as data validated criteria for cell A2.
…
Tom’s Tutorials For Excel: Summing and Averaging Dynamic Lists, Including or Excluding Blank Cells
The below picture shows a side-by-side comparison of summing and averaging the last 5 cells in a dynamic list, depending on if blank cells should or should not be included in the formula results.
…
Tom’s Tutorials For Excel: Summing Historic Numbers by Date, Excluding Weekends or Weekdays
When you have a list of numbers for previous dates, such as with payroll or sales activity, here is an example of how you can sum the past 14 days for weekdays only and for weekends only.
…
Tom’s Tutorials For Excel: Finding Matches Among Horizontal and Vertical Ranges
Here is how you can verify if a matching value is found in both a horizontal and vertical range. In Picture #1 a match is not found, but in Picture #2 a match is found.
The formula in cell A1 is
=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,MATCH(A3:E3,F6:F17,0)))+0=1,”Found”,”Not found”)
Conditional formatting is applied to cell A1 for two conditions.
…
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: Calculating Negative Time Differences in Hundredths of a Second
When you want to calculate differences in time when the measurements are in hundredths of a second, special consideration must be given for cases when the result is negative. The next pictures show examples of calculations when the Actual time is less than expected;
…
Tom’s Tutorials For Excel: Making Your Hyperlinks User-Friendly
When you put hyperlinks in cells on a worksheet, it’s a good practice to present them in a user-friendly, intuitive way. For example, the pictures show an original list of hyperlinked cells with their Microsoft-induced wordy screen tips. The modified list was created by the below macro to show those same cells more intuitively,
…
Tom’s Tutorials For Excel: Conditionally Formatting Early, Late, and Acceptable Times
Here is how you can use Conditional Formatting to identify times that are more than, or less than, a margin of acceptability. In the picture, the cells holding airline flight arrival times that occur more than 60 seconds earlier than their scheduled arrival time are shaded yellow.
…
Tom’s Tutorials For Excel: Analyzing Named Ranges with the INDIRECT Function
The INDIRECT function can refer to a named range for quick data analysis, especially if you don’t need or want to use a pivot table. In this example, columns B:F hold several years of daily sales activity for a department store.
…
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: Using Label Headers as Intersecting Lookup Criteria
I previously posted this example of using the spacebar character as the mathematical operator in a formula to sum numbers in the intersecting range of multiple rows and columns. Also is this other example of using a formula to lookup an intersecting value.
…
Tom’s Tutorials For Excel: Copying Only a PivotTable’s PivotField to Another Worksheet
Here is how you can copy a specific pivot field and display its data on another worksheet. In the following pictures, only the columns of a pivot table’s Sales field are copied to Sheet2 so you can work with that Sales data independent of the original pivot table.
…
Tom’s Tutorials For Excel: Reverse Lookup For Times in Milliseconds
You can format, analyze, and lookup (in this example, reverse VLOOKUP with CHOOSE) times measured in milliseconds.
In the first picture, times for a swimming race are measured in minutes, seconds, and milliseconds. The times in range B4:B12,
…
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: 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.
…
San Francisco, California
1-925-310-4170
tom@atlaspm.com
