Intermediate Excel Guides
Practical guides for Excel users who have the basics down and want more capable formulas, analysis, and spreadsheet workflows.
88 articles
How to Use XLOOKUP with Wildcards for Partial Matches
Learn how to use XLOOKUP with wildcards in Excel for partial matches, contains searches, and messy text that does not match exactly....
How to Return Multiple Columns and Rows with XLOOKUP
Learn how to use XLOOKUP to return multiple columns or rows in Excel and how spill behavior changes the way lookup formulas work....
How to Use XLOOKUP with Multiple Criteria in Excel
Learn how to use XLOOKUP with multiple criteria in Excel by combining Boolean logic or concatenation for cleaner, more flexible lookups....
How to Create a Drop Down List in Excel with Data Validation
Are you wondering how to create a drop down list in excel? Don't worry. Our step-by-step guide tells you everything that you need to know....
How to Calculate Hours Between Two Dates and Times in Excel
Learn how to calculate hours between two dates and times in Excel using simple formulas for elapsed time, decimal hours, overnight shifts, and workdays....
How to Remove Duplicates in Excel: An Easy Guide
Are you wondering how to remove duplicates in Excel? Check out our easy guide for step by step instructions and all the information you need....
How to Alphabetize in Excel: Fast & Simple
Using Excel we can properly organize data for our businesses or personal finances. Learn how to alphabetize in excel so it is easy to read and refer back to whenever necessary....
Pivot Tables (PV Table): Everything You Need to Know
When working with Excel spreadsheets, it can be difficult to extract the information you need from large sets of data. Pivot Tables (pv table) offer a great way to quickly condense and analyze, and present your data, all...
How to Extract Time from DateTime values in Excel
Discover how to skillfully extract time from datetime values in Excel. Learn simple techniques to manage and analyze your data efficiently in this step-by-step guide....
Extract Date from DateTime Number in Excel
You can extract the Date value from a Date-Time number using the INT function in Excel. Then change the cell formatting to a Date format....
Add Macro Button to the Toolbar in Excel 2011
Here is a short tutorial on how to add a macro button to the toolbar in Excel 2011, with a few tips on how to change the button image....
Control + Home in Excel for Mac
I figured out how to get a Control + Home keyboard shortcut to work in Excel 2011 (for Mac). It's an Excel Windows favorite of mine....
A Dynamic Dependent Drop Down List with a Horizontal Table Reference
I received a comment asking if a dynamic dependent drop-down list in Excel could have a list where the "table headers were actually rows and not columns?" Since...
How to Update a List or Range without OFFSET
I avoid the use of Volatile Functions , especially OFFSET, which is commonly used to update a list or range. They can slow down the operation of your workbook....
Select Excel's Used Range on a Mac
Selecting the used range in an Excel worksheet is complicated by not having a Home button on the Mac keyboard. Some VBA code can solve this problem....
Pay Periods and Funky PivotTable Controls
Use a PivotTable to find how many months in a year have an extra pay period. Excel 2011 PivotTable controls are funky looking, comparatively speaking....
Fill Down a Formula with VBA
Here is some VBA code I use to copy a formula down to the end of a data range. It assumes a heading in row 1 and the formula in row 2....
A Dynamic Dependent Drop Down List in Excel
Learn how to create dynamic dependent drop-down lists in Excel....
Dynamic Table Reference with INDEX
A Defined Name with the INDEX function gives a dynamic range reference to a Table column, but moving a Table column can invalidate the reference....
Total Row Formulas in Excel Tables
Excel Tables have a Total Row that can be toggled on and off from the Ribbon. Several functions are available when the Total Row is turned on. In Excel 2010 and...
Formulas and Structured Data in Excel Tables
Tables automatically extend formulas when data is entered. If a formula is changed in a Table cell, all rows in the column are automatically modified....
Excel Tables - Styles, Conditional Formatting and Data Validation
Format Excel Tables with Table Styles, Conditional Formatting, and Data Validation. Turn off worksheet gridlines to make the Table more distinctive....
Fat Fingers on the Space Bar - A Conditional Formatting Example
There is a Conditional Formatting solution for entering a blank or space character into a cell. A space character causes #VALUE! errors in formulas....
How to Quickly Convert 1904 Dates to the 1900 Date System in Excel
Learn how to convert 1904 date system values to the 1900 date system in Excel. This guide covers methods for accurate date and date systems management in Excel....
Use Formula Auditing to Help Explain Formulas Excel
When you need to present a complicated spreadsheet, the Formula Auditing feature in Excel visually helps to explain formula relationships....
Documentation for VBA in Excel
Documentation for VBA in Excel is arduous yet can save you loads of time when you have forgotten the details of a project....
How to Copy Data to Another Worksheet with Advanced Filter
Unlock the power of Excel's Advanced Filter to seamlessly copy and organize data between worksheets. Learn techniques for precise data migration and efficient workflow....
Sorting with Custom Lists in Excel
Sorting with a Custom List makes things simpler when Excel doesn't have a default sort order to fit your needs....
A Woopra Chat About Excel
Convert minutes and seconds to a value of minutes followed by the text MIN and formatted correctly with no decimal places by using the TEXT function....
Excel 2011 Keyboard Shortcuts on Your Desktop
Locate Excel 2011 Keyboard Shortcuts Help web page and save to your computer for off-line access via your web browser, Firefox, Chrome, or Safari....
Import Data From the Web in Excel
Import data from a web page into an worksheet with Excel 2010 and 2007 (windows versions). This external data query can be refreshed if data changes....
INDEX and MATCH Functions Together Again in Excel
The INDEX and MATCH functions perform a lookups in Excel. INDEX returns a cell from an array, MATCH contributes the row and/or column position....
The INDEX Function in Excel
The INDEX function returns a cell value from a range, given a row and/or column position number. Three examples are given in this article....
The MATCH Function in Excel
The MATCH function in Excel returns the position number of a matched value from within a range, not the value itself....
Goal Seek in Excel
If you know the result that you want from a formula, but are not sure what input value the formula needs to get that result, use the Goal Seek feature...
What Day of the Year is It?
The embedded spreadsheet will take a date value and return the Day of Year with a formula that uses the DATE Function....
Use the DATEDIF Function to Quickly Calculate Date Difference in Excel
Use the DATEDIF Function in Excel to find the number of Years, Months and Days between two dates. Only documented in Excel 2000 but in later versions....
The Repeat Command in Excel for Mac
Add the Repeat command icon to the Standard Toolbar in Excel for Mac, 2011 and 2008, from the Customize Toolbars and Menus dialog box....
Redo and Repeat Commands in Excel [Updated]
The Repeat command is useful in Excel and intertwined with the Redo command. Add the Repeat command to the Quick Access Toolbar to discover its uses....
Fixing the F4 Button in Excel for Mac
The F4 key in Excel for Mac is a shortcut for the Paste command, which can be reassigned to the Redo command so it works similar to Excel for Windows....
The TRIM Function in Excel and VBA
The Excel TRIM Function removes all spaces from text except single spaces between words. The VBA TRIM Function removes leading and trailing spaces....
VLOOKUP Function in Excel: The Essential Guide
The VLOOKUP function in Excel explained in relatively non-technical terms for the laymen. Two examples, FALSE and TRUE, from the fourth argument....
VBA Help System Lacking in Excel 2011
The VBA Help system for Excel 2011 for Mac is a tangled web of confusion that's frustrating and difficult to say the least. Excel 2010 is much better....
ISNUMBER vs IsNumeric in Excel: Worksheet and VBA Differences
Learn the difference between Excel ISNUMBER and VBA IsNumeric, including how they handle text numbers, blanks, dates, and data-entry cleanup....
Microsoft Office 2011 Document Connection
The Microsoft Document Connection program comes with Office 2011 for Mac and is a great way to access your SkyDrive files in the cloud....
Equation Editors in Excel 2010 and Word 2011
The Equation Editor is new in Excel 2010 and designed to create a mathematical equation as graphical object. Word 2011 has the same Equation Editor....
Converting Pace in Minutes per Mile to MPH and Back Again in Excel
I'm converting Pace to MPH and then reversing the process, converting MPH to Pace, to create a conversion chart....
3 Proven Formulas to Calculate Letter Grades in Excel
There are several ways to turn student scores into letter grades in Excel. Here we guide educators and academic professionals to manage a gradebook using Excel....
Nested IF Functions in Excel
Nested IF Functions means having at least one IF Function contained within another IF Function, which can get complicated. Use a binary outcome chart....
Multi-Cell Array Formula in Excel
A Multi-Cell Array formula is entered by selecting a range, typing a formula, then holding the Control and Shift keys down while pressing Enter....
Update Excel 2003 to Use Newer Excel Files
Install the Office Compatibility Pack so you can open Excel 2007 and 2010 workbook files from older versions of Excel: 2003, 2002, or 2000....
Hide Worksheets in Excel
Change the visible property of a worksheet to make it very hidden and won't show in the Unhide sheet dialog box....
Use the DATE Function to Find the Last Day of a Month
The DATE Function will return the last day of the month prior to the Month argument, by using zero (0) for the Day argument....
Introduction to PowerPivot
PowerPivot works with Excel 2010 to import millions of rows of data from different sources, build relationships, and visualize data with PivotTables....
A Sparkline Example in Excel 2010
Create a dynamic range sparkline graphic to show the last seven data points. Sparklines are new in Excel 2010 and don't work with earlier versions....
Put an OFFSET Formula Inside a Named Range
Create a Named Range using a formula with the OFFSET Function to capture the last 7 days of a range....
The OFFSET Function - Last 7 Data Points
Use the OFFSET Function to return the last 7 rows of a range....
Become the OFFSET Function and Tell a Short Story
I become the OFFSET function to tell a simple story of how I give you a value for the last cell in a range by using an example....
How to Convert Decimal Hours to Time in Excel
Master the art of converting decimal hours to time format in Excel. Easily track minutes and hours In work and log books, events and when analyzing time data....
Keep Track of Passwords in Excel [Updated]
Keep track of passwords in Excel with a password protected file....
Enter a Constant Value in a Name
Create a Name in Excel that holds a constant value....
Name and Select a Range with the Name Box in Excel
Name a cell, range, or non-contiguous range by using the Name box, then go quickly to that Named Range....
Use the Name Box to View Recently Used Functions in Excel
Quickly see the Excel functions you've recently used with the Name box....
Copy a Worksheet Picture in Excel 2003 or Excel 2008
Copy a picture of a spreadsheet in Excel 2003 or Excel 2008 for Mac and paste into your presentation software or elsewhere....
Insert Screenshots in Excel 2010
Insert an image into an Excel 2010 worksheet from any program that is currently running on your computer and not minimized to the task bar....
Save Time Entering Decimal Points in Excel
Have Excel automatically enter fixed decimal places for you....
Figure Those Pesky Fractions in Excel
Enter fractions in Excel by adding a space after the integer and before the fraction. Easily create a list of fractions.....
Stock Price Formula in a Spreadsheet
The GoogleFinance Function returns a stock price by default, given stock symbol for a company or mutual fund....
The TEXT Function in Excel
The TEXT Function in Excel converts a value to text and lets you specify the display formatting by using special format strings....
The WEEKDAY Function in Excel
Excel 2010 has new function arguments for the WEEKDAY Function. It returns an integer value based upon the day of the week....
Convert Seconds to a Time Format in Excel
Convert a large number of seconds to a hours:minute:seconds format using the TIME, INT and MOD functions....
Putting the TIME, INT and MOD Functions to Work in Excel
Convert a Time in seconds to a minutes:seconds format by using the INT and MOD functions inside the TIME function....
The TIME Function in Excel
Using the TIME Function to display minutes and seconds in a readable time format....
The MOD Function in Excel
Understanding how the MOD Function works to extract the number of seconds left over after dividing a number of seconds by 60....
QUOTIENT, ROUNDDOWN and INT Functions in Excel
Three functions to get the integer portion of a division calculation: QUOTIENT, ROUNDDOWN, INT Functions in Excel....
iTunes Playlist Export Task – Convert Seconds to a Time Format
Converting seconds to a minutes:seconds time format requires several formulas....
Convert a Text String Date to Date Serial Number
One formula to convert a text string date to a date serial number using double negation, MID Function, Ampersand Operator, and the RIGHT Function....
The VALUE Function in Excel – Converting Text Dates
Converting text string Dates to numeric serial number values using the Excel VALUE Function and with alternate methods: addition, multiplication, division, and double negation....
The TYPE Function in Excel
The TYPE Function in Excel will indicate the type of data in a cell. In this case between Text and Numeric....
CONCATENATE Function or Ampersand Operator in Excel
Choosing between the CONCATENATE Function or Ampersand Operator in Excel. Ampersand is easy and simple....
The RIGHT Function in Excel
The RIGHT Function returns a specified number of characters from the end of a text string in Excel....
The MID Function in Excel
Use the MID Function to extract characters from the middle of a text string in Excel....
Removing Duplicates in Excel 2008
Remove duplicate values from a data set in Excel 2008 for Mac....
Removing Duplicate Values in Excel 2003
In Excel 2003 removing duplicate values is a manual process. Here is a method that worked well for me on this particular data set....
Text To Columns in Excel
Parse text data with Excel's Text to Columns feature....
Open a CSV File
I have a Lose It! app on my iPhone that automatically backs up data to my web account. The website makes it easy to download reports using CSV files, essentiall...
Generate a List of Dates in Excel
Generate a date series in Excel quickly with the Edit, Fill, Series pop-up box....
Microsoft Excel Date Systems for Windows and Mac
Excel date systems for Windows and Mac are drastically different and can be confusing. Sharing Excel files between Windows and Mac computers can be confusing because of the two different date systems....
About Excel Semi Pro
Since 2010, we have been helping people improve their Excel skills. Here, you will find beginner and intermediate tips on navigating spreadsheets to achieve results.
Read more about us