What's New in Microsoft Excel

Here is the latest news about Microsoft Excel, and updates on the Contextures website and blogs.

Also see: Excel Events || RSS || Excel Humour || Debra D Blog

Latest News

Excel Error: Have you ever seen this Excel pivot table refresh error, with Data Model data?

Pivot Items: Use this Excel macro to keep one pivot item collapsed, but allow other items to expand

Excel Newsletter: Read the latest issue - Formula challenge, quick backup tool, and more. Don't miss the next issue - sign up to get it by email.

Also see: Excel Events || Excel RSS || Weekly Excel Humour || Excel Products

Previous News

Excel Roundup: Charts, data tips, #Excel tools, and more, in the latest Excel Roundup.

Date Formula: This Excel formula finds Nth weekday in a month, such as the 4th Thursday in November

Filtered List: Create an Excel pivot table using only the visible rows in a filtered list

Fix Pivot Labels: Quick fix for incorrect pivot item captions in Excel. Use a macro or do a manual fix

Hyperlinks: Create an Excel hyperlink formula that takes you to the next empty cell, for data entry.

Pivot Table Add-in: Get the latest version of my Excel Pivot Power Free add-in, with time-saving tools

Dependent Drop Down: Make dependent drop down lists with new Excel functions and dynamic arrays

Copy/Paste Problems: How to fix Excel copy and paste problems, when working with multiple selections

Pivot Table Buttons: Hide Excel pivot table buttons and labels, to prevent accidental changes to the layout

Excel Notes: Do you keep notes about your complex Excel formulas and macro? What's your system?

Filter Macros: Use macros to change Excel filters, on worksheet, or in named table

Show Rank: How to show rank in an Excel pivot table, to make numbers easier to compare

Pivot Refresh: After changes in pivot table data, do manual or auto update to pivot table, to see the latest information

Slow Macros: Why does an Excel custom function (UDF) run for no reason, making macros slow?

Count Duplicates: Count how many times each number set appears in an Excel list, in any order

Debra D Blog: How to stop your keyboard changing language accidentally.

Spreadsheets: Tips for good charts, data prep, formula coach and more, in this spreadsheet roundup.

Dashboards: Fun for a dashboard - Type a number on the worksheet and a Happy Face or Plie Slice shape changes colour automatically. An angle in the shape changes too!

Printing: Save time with these Excel printing tips. Videos show the steps

Debra D Blog: How to fix Dreamweaver if it takes a long time to start.

Line Chart: Set up an pivot chart with lines to compare annual data month by month.

Pivot Fields: Quickly remove a pivot field from an Excel pivot table layout

Loan Payment: Show loan payment details in #Excel with PMT and IPMT functions

Highlighting: Highlight temperature highs/lows, and weather types, like snow or sun, with Excel;s conditional formatting

Slicers: Connect Excel Slicers to multiple Pivot Tables, and filter all the pivot tables at once

Drop Down: Make a dependent drop down list in Excel. List changes based on value in another cell

Spreadsheet Day: Happy Spreadsheet Day 2019! Are you planning any special functions or cell-ebrations?

Source Data: If a pivot table doesn't show new data after a refresh, find and adjust its source data range

Save Time: Save time with this Excel data entry trick. Pick from a list of previously entered items

Quick Count: How many people picked June? Get quick counts with an Excel pivot table.

Excel Macros: See how to make minor changes to Excel macro code, such as changing the sheet names

Pivot Table Macro: Use this macro to remove pivot table calculated fields in Excel

Special Characters: See how to insert emojis in Excel worksheets, to add a bit of humour (when appropriate!)

Pivot Charts: Save space in your Excel dashboard - update all pivot charts with a small in-cell filter

Spreadsheets: Calculators, beer, baseball + more, in the latest spreadsheet roundup. And remember, Oct 17th is #SpreadsheetDay

Bulgaria: Attend Bulgaria Excel days, November 20-23, in Sofia, Bulgaria. The main conference is Nov 21st, with optional master classes on the days before and after.

Fun and Games: Take a short break and enjoy some fun and games with Excel

Pivot Fields: Use this easy trick to add or move Excel pivot table fields

Date Range Filter: See two ways to filter an Excel table for a specific date range - use a date filter, or try a Filter by Color trick.

Pivot List: Use this #Excel macro to list all pivot tables and pivot fields in a complex workbook

Weekly Planner: Customize this Excel weekly planner, and print a copy each week. Conditional formatting and hidden formulas change the planner's look

Old Items: Fix old items that stay in pivot table lists, after you delete them from the data. Fix one pivot table, and default settings for new pivot tables

Charts: Pick date range from drop down lists, and Excel chart updates automatically, with data for selected dates

Pivot Tables: Create a simple calculated field in a pivot table in Excel, to show the bonus that each sales rep will get

Training: Excel is awesome, but Ben Collins shows the power of Google Sheets formulas. And for Power BI training, check out the Australian Summit in August. Get the details on my Contextures Blog

Pivot Tables: Use these macros to sort pivot fields by value. Special code for data model pivots

Excel Roundup: Charts, data viz, functions, beer, and more - Excel Roundup for Summer 2019

Pivot Tables: Quickly unpivot Excel data with Get & Transform, with no changes to source data

Excel Tips: What are your top Excel tips for beginners? My tip - Save! Use this macro for quick backups

Pivot Formulas: Quickly list all pivot table formulas with a built-in Excel command, or with a macro

Excel Files: Use this macro to list all Excel files in a specific folder, with size and date info

UserForm: How to show an Excel UserForm automatically when a workbook opens

Pivot Error: What causes the pivot table error "Cannot group that selection" in Excel?

Lookup Formula: Formulas to find the last number, or last text/number, in an Excel column

Distinct Count: How to show a distinct count (unique count) in data model pivot tables in Excel

Sheets Macro: Add month sheets automatically in Excel, based on a Master sheet. New sheets named for month and year. Video and written steps

Pivot Table Dates: How to undo or stop pivot table date grouping in #Excel 2016 and later versions

Excel Files: Have you seen these Preview Picture problems in Excel?

Pivot Table Errors: Why do Excel pivot table grand totals show errors, even when there are no errors in the item amounts?

Pivot Table Totals: Errors in Excel pivot table grand totals, but no errors in column

Pivot Table Names: What are the rules for naming an Excel pivot table?

UserForms: Get the new UserForm Builder add-in bonus if you've bought a copy of my Excel UserForms for Data Entry kit

Pivot Tables: Make a quick list of key pivot table option settings in Excel with this macro

Pivot Tables: How to copy pivot table values and formatting, manually or with a macro

Status Bar: Do you customize your Excel Status Bar, or keep the default settings? See all the options, and choose what you need.

Pivot Tables: How to change the formula for a pivot table calculated field. Video and written steps

Styles: Make custom styles for Excel tables and pivot tables, copy to other workbooks, and avoid problems

Pivot Tables: See how to make minor changes to pivot table macros, so they work for a specific pivot table, or all pivot tables

Macros: Click one of the worksheet buttons, and a macro records the date, time, and the button number. See the collected data on a different sheet

Pivot Tables: Use this macro to list pivot field details for a Pivot Table - name, sample data, calculated field formulas, and more

Excel Tables: Use a simple formula to quickly compare two Excel tables - see if they're exactly the same

Pivot Tables: Use this macro to list all Pivot Tables in a workbook, with source or MDX info

Buttons: Link a worksheet button's text to a cell, for a dynamic caption

Pivot Tables: Macros quickly remove fields from Excel pivot tables - normal or OLAP (Data Model).

VisiCalc: Interesting VisiCalc demo by Dan Bricklin shows its interaction techniques

Pivot Tables: Create a detailed list of all pivot tables in your workbook, to use with troubleshooting or workbook documentation.

Error Values: Is it evil to show custom #Excel errors, instead of the built-in hash errors?

Pivot Tables: Clean up pivot table subtotals, to make the data easier to read

Debra D Blog: I tried an ad blocker, and it caused more problems than it solved

Spreadsheet Roundup: Personal finances, spreadsheet tab patents, and more, in this month's roundup on the Spreadsheet Day blog.

Order Form: See how to make an order form in Excel - follow the step-by-step video instructions

Pivot Tables: Show numbers as text in Excel pivot table values, with conditional formatting and a custom number format. Set this up manually or with a macro.

Drop Downs: Create an Excel drop down that only shows the visible items from a filtered list

Pivot Tables: Short video shows difference between Excel pivot table calculated item and calculated field, and how to get a list of all the formulas used

Debra D Blog: Keeping track of your ideas, creating new ideas, and a fun little quiz.

Tables: How to add new data to an #Excel table with the Total Row showing

Pivot Tables: How to hide or show the Values Row in an Excel Pivot Table, and why it sometimes doesn't change anything

Debra D Blog: Seeing things in black and white - memories of old computers and photos

Top/Bottom: Highlight the top or bottom values in an Excel list, with a flexible rule that's linked to a worksheet cell

Pivot Tables: Use these macros to allow or block Excel Pivot Table features, and prevent changes

Debra D Blog: Find public domain images and try some art before breakfast

Trackers: Use these free Excel trackers to help with your 2019 health and fitness goals. Enter daily data and see a summary

Pivot Tables: Instead of typing your pivot chart title, use a worksheet formula to create a dynamic title.

Debra D Blog: Setting business goals for 2019 - what's your plan?

Data Entry: Allow weekend dates only in Excel, with a Data Validation custom formula that blocks Mon-Fri dates

Pivot Tables: Stop Excel from automatically adding GetPivotData formulas when you click on a pivot table value

Data Entry: Excel worksheet data entry form reminds you to save changes. Alerts created with formulas and conditional formatting

Calendars: Get this simple Excel Advent Calendar, no macros, uses background picture, formulas and formatting to show a different picture each day, from December 1 to 24

Planners: Get organized with free Excel Holiday Planners. Budget, event calendar, task list, dinner planner and more

Functions: Combine two functions to do an Excel VLOOKUP to the left, without changing your table layout

Pivot Table Styles: Make a quick list of a workbook's pivot tables & styles, with colour samples. Get the Excel file with macros and buttons to test

Excel Tips: Ready to leave work? Quickly close all Excel files with these workarounds and shortcuts

Tricks: Interactive Excel Halloween tricks workbook, Formulas & formatting, no macros. Click to change pictures, colours, messages

Microsoft Access: On my Debra D blog, see how to create RSS feed entries in Microsoft Access

Excel Roundup: Read the Excel Roundup for Fall 2018. Excel tips, automation, data model info, Microsoft events, and more.

Spreadsheet Day: Here are a few of the highlights from Spreadsheet Day 2018. Thanks for celebrating!

Data Validation: What causes missing drop down arrows in Excel data validation? See how to fix or prevent the problem.

Spreadsheet Day: Happy Spreadsheet Day, October 17, 2018! Are you planning any special functions?

Spreadsheet Day: Get ready! Spreadsheet Day is October 17th -- how will you celebrate this year?

Excel Ribbon: Put your own Excel macros on this custom Ribbon tab, by making simple changes on the worksheet. Video and written instructions.

Excel Ribbon: Strange Excel command to cycle font color in the selected cell, and how to find button image names for a custom Ribbon tab.

Conference: See new ways to automate Excel, at the Develop Excel conference in London, on Oct 18th. Register free, or with contribution.

Pivot Tables: Macros name drilldown sheets when you double-click to create them, and delete some or all drilldown sheets when closing file.

Comments: Excel threaded comments are coming! Be careful - legacy comment formatting is lost, if you switch while editing.

ComboBoxes: Click a button on the worksheet, and a macro runs, to select the next item in the Combobox list. If the last item is currently selected, the next selection is the first item in the list.

Comments: Use these Excel comment macros to resize, move, or format comments. Thanks to J. Woolley, who contributed the code and sample file.

Pivot Table Macro: Use this macro to remove "Sum of" or "Count of" from pivot table headings. New macros work with data model pivot tables too.

Develop Excel: The London Develop Excel Conference is aimed at people responsible for developing Excel addins and solutions. This one-day conference will be held in London, October 18th, 2018, and the ticket cost for this non-profit event is only £100. Speakers include heavyweight developers for Excel and .Net, Python, C++, VSTO, and Javascript. See details on the conference website.

Secure Site: I've switched the Contextures site to HTTPS, so you should see the "Secure" symbol on every page. If you spot any problems, or pages without that symbol, please let me know! Big thanks to Steve Friedl, a former Enterprise Security MVP, and Linux expert with 30+ years experience.

Excel Roundup: Read the Excel Roundup for Summer 2018. Better charts, data analysis tips, skills test, and more

Drop Downs: Create multiple dependent drop downs in Excel - region, country, area, city. Requires 2 tables, 3 named ranges, no macros

Lookup Table: Use Excel INDEX with 2 MATCH functions for mileage lookup between cities.

Hyperlinks: Use enhanced hyperlinks in Excel, to link to chart sheets, or activate a sheet without selecting a cell. Technique by J. Woolley.

Pivot Tables: Use this macro to create a list of all the pivot table styles in your workbook (built-in and custom), with colour samples

Exipry Dates: Keep track of expiry dates in Excel - highlight items that will expire soon, so you won't miss them.

Hyperlinks: Use Excel hyperlinks to run command or script files with a single click. Also, SuperLink UDF improves on Excel's HYPERLINK function.

Task List: Use this quick trick to automatically cross off tasks in an Excel To-To list. No macros - just easy formatting.

Compare Cells: Use formulas to compare two cells in Excel. Do a simple test, or use a complex formula to see what percentage of characters match

Hidden Data: Show warning for hidden rows or columns on Excel worksheet

Excel Functions: You can do almost anything in Excel! Calculate Easter dates with formulas or UDFs, and use functions to change an IP address to the new format

Data Model - Are you confused by Excel's data model? Sign up for a free live webinar, Build an Awesome Data Model in 7 Easy Steps, from Excel TV. Pick a time on March 29 or 30.

Excel Roundup: Read the Excel Roundup for March 2018. Data viz, Excel tips, odd facts, upcoming Excel events, and more

Chart Color: Change Excel chart colors with a macro, based on rank. This is a workaround for a problem with getting colors from a conditional formatting color scale

Macros: Learn how to automate Excel, and save time! Sign up for the new one-hour free Macros & VBA Webinar from John Michaloudis. See how to record a macro, and set up a button to run it. Learn how to save and share your macros.

Seating Plan: Get this free Excel seating plan with charts, and modify it to show your guest names and table layout. See how to use the sample file, and how to add more guests and tables

Pivot Tables: Fun with Excel pivot tables & Olympic hockey player data. Did younger/taller/heavier team win?

Budget Report: Click a Slicer to quickly change an Excel budget report - show Forecast, Actual, YTD or Variance

VLOOKUP Error: Troubleshoot an Excel VLOOKUP formula error, to find and fix the problem, and get correct results

Drop Downs: Show a warning in Excel drop down list, if there is a problem in source data. Dependent drop down shows customer names for selected region if source data okay

Excel Tips: Save data entry time with AutoCorrect shortcuts, and use these macros to copy your AutoCorrect list to another computer

Functions: How to fix problems counting Excel data. Character limit creates errors in COUNTIF, cells that look empty are counted with COUNTA

Pivot Tables: How to fix problems when grouping pivot table items, if you get an error message, "Cannot group that selection."

Functions: Count items in a cell with Excel's SUBSTITUTE, LEN and TRIM functions. Check for minimum count, like 3 items in valid address, "123 Main St"

Table Filter: Quickly filter an Excel table with a spin button - click up and down buttons to filter by next or previous item in the list.

Excel Roundup: Read the Excel Roundup for Dec. 14, 2017. Free Power BI book, dashboard tips, Excel complaints, Christmas tree, and more.

Names: What are the rules for Excel names? You might be surprised by some of the characters that are allowed

Planning: Get ready for the holidays with this Excel Holiday Planner Workbook. Budget, event calendar, task list, dinner planner and more.

Debra D Blog: When you finally learn about a cool Outlook search feature, then find out they're taking it away

Subtotals: Do you still need Excel subtotals? Should you use AGGREGATE or SUBTOTAL? Pivot tables or the Subtotal feature?

Troubleshooting: Data validation troubleshooting macros and tips -- find all the data validation cells in a workbook, and list all the rules.

Power Query: Become a Data Wizard and master your data in Excel and PowerBI. Ken Puls and Miguel Escobar are offering their next online workshop November 15-16, 2017, with follow-up Q&A on November 29th. Click to get the full course details and registration information. Space for this session is limited, so don’t miss out!

Excel Macros: Simple steps to create a tartan pattern in Excel -- base your tartan on a traditional pattern or make your own! Free workbook with Ribbon commands to make it easy.

Lookup Formula: Can you solve this Excel lookup formula challenge? Write a formula to find the customer code for each row in the imported data.

Excel Macros: Excel formulas are awesome, but you can add more power to Excel with macros. To help you get started, Jon Acampora is hosting free webinars that show you 7 of the basic steps. The webinars available this week only -- October 23rd to 27th. This webinar is for beginners, so don't worry that it will be over your head -- Jon will explain things clearly and simply. Sign up now -- click here to pick a day and time to attend. (You can watch the replay later, if you miss your session).

Pivot Trick: Use this quick trick to add or move pivot table fields. Did you know that you could do this? Most people don't!

Debra D Blog: How to build date queries in an Access database, using criteria from a form text box, or pop up prompt

Spreadsheet Day: Thanks for celebrating Spreadsheet Day 2017! Here are a few highlights from yesterday's articles and tweets

Data Bars: Show Excel Data Bars, then use MIN and MAX formulas to adjust the bars.

Spreadsheet Day: Happy Spreadsheet Day 2017! (Tuesday, October 17th) How are you celebrating?

Spreadsheet Day: Are you ready to celebrate Spreadsheet Day 2017? It's next Tuesday, October 17th.

Dashboards: Learn how to build interactive dashboards with Power BI and Excel, in a free one-hour webinar by Mynda Treacy. Go from raw data in Excel, to sophisticated dashboards, in a few simple steps, using free tools. Register today -- the webinars are available for a limited time.

Pivot Tables: See how to make quick copies of a pivot table - one copy for each item in a Report Filter. Or use a macro to make copies of the entire sheet.

Pivot Table Percentages: Show pivot table values as percentages, to make it easy to compare totals. See % of Column, % of Parent Row and % of Parent Total.

Pivot Tables & BI: Ken Puls (ExcelGuru.com) sits down with the team from Excel TV to show how you can use pivot tables to build Business Intelligence (BI) solutions in Excel. There are 3 free videos in the series, so click here to get started.

Pivot Table Totals: Use the Show Values As feature to show pivot table totals as a percentage of a parent field amount. For example, % of Region sales for blue binders.

Hyperlinks: Use Excel hyperlinks to run command or script files with a single click.

Excel History: Do you remember the early days of Excel? Old Excel tips (still useful) and version 2.2 review highlights.

Functions: Convert measurements in Excel with drop down lists of units. Kg→Lb, Ft→Km and many more measurement types.

Debra D Blog: Should you use SamCart or E-junkie for digital product sales?

Debra D Blog: Missing month headings in Access report- easy fix but hard to find!

Excel Slicers: Pivot table trick saves space - click filter cells to show on demand Slicers. Perfect for sheets with multiple Slicers.

Excel Slicers: Hide Excel Slicers until you need them - a worksheet button opens this pop up selector

Error Checking: In his new course, Ken Puls shows how to build an error checking system in your workbooks. Be confident that everything is calculating correctly, even if you haven't looked at the workbook in months! See how to create an error checking section on each worksheet, and a global check point for the entire workbook. The system even checks the pivot tables, to see if they've been updated -- that can prevent serious problems! Follow the course online, or download to watch offline too.

Pivot Table Macros: Use macros to create Excel pivot tables from a specific existing pivot cache

Excel Shortcuts: Add commands to the Excel Quick Access Toolbar, and export your settings, to use later

Pivot Power Premium: There was a major update to my Pivot Power Premium (PPP) add-in. New features - click a button to quickly get the settings from any pivot table, then 1 click to apply those settings to any other pivot table, or all pivot tables. To get the update, use your latest download link.

Excel Training: Mynda Treacy has just opened registration for her highly-rated Excel Dashboard Course and Power BI Course. I highly recommend these courses, so don't miss this chance, if you're interested.

Free Webinars: If you're not ready for the full courses, attend one of Mynda's free one-hour webinars on Power BI or Excel Dashboards. They're only available for a limited time -- June 27th to July 13th.

Excel Skills: What are the key Excel skills for specialists in your profession? Which functions or features do you use most often?

Training - Learn more about Pivot Tables or Power Query, in the free 1-hour webinars that John Michaloudis is hosting. Pick a date and time that fit your schedule.

Excel Tips: Read the Excel Roundup for June 2017 - shortcuts, favourite Excel functions, and more

Hide Used Items: To prevent people from selecting the same item twice, hide used items in a drop down list.

Pivot Table Problem: How to troubleshoot an Excel pivot table with missing pivot items

Filter a List: Two ways to filter Excel data for items in a list: 1) exact matches only, 2) partial match in cell contents.

Fiscal Year Summary: Line up weekdays in a pivot table, to compare sales data for two fiscal years. Formulas in source data calculate fiscal week and weekday.

Excel Macros for Beginners: Allen Wyatt, from the Excel Tips website, has 4 free videos that give you a basic introduction to macros, and show how they can help you work more efficiently.

Excel VBA: Join Jon Acampora for a free online webinar -- The 7 Steps to Getting Started with Macros & VBA. Sessions are available from May 8-12, 2017, so pick the date and time that works best for you.

Excel Tips: Read the Excel Roundup for May 2017 - troubleshoot formulas, interactive charts, and many more tips

Price Lookup: How to do a price lookup based on an invoice date and product name.

Rounding: See 7 ways to round numbers in Excel.

SUMIFS Function: Adjust a SUMIFS formula so it works with empty criteria cells

Pivot Format: Have you seen this problem? Pivot table fill colour disappears if you use a shortcut to apply it

VLOOKUP: Combine VLOOKUP and MATCH functions in Excel to create a flexible formula

Pivot Caches: Use this macro to create a list of all pivot caches in an Excel workbook

Fix Names: Use formulas to change names to proper case, and count the fixed names

Pivot Table Refresh: How to refresh an Excel pivot table on a protected sheet, manually or with a macro

Freeze Panes: Tips on how Freeze Panes works, and a macro to freeze all sheets at the selected location

Conditional Formatting: How to fix Excel conditional formatting duplicate rules if they are automatically created

Pivot Tables: To print multiple versions of an Excel pivot table, use this macro

RANK IF: Use an Excel Rank IF formula to rank a number within a specific set of numbers.

Excel Sheets: Keep a list of sheets open, so it's easy to navigate in a large Excel workbook.

Pivot Tables: Pivot table report filters don't have Sort options. Use this macro to quickly sort all the report filters

Subtotals Problem: Pivot table subtotals have changed, for grouped items, in the latest version of Excel 2016 (subscription). Learn about the change, and how to avoid problems.

Dashboards: Ben Collins shows how to build interactive dashboards with free Google Sheets tools, in his new course.

Pivot Tables: Use a macro to list all pivot fields and pivot items in an Excel pivot table

Worksheet Buttons: How to change Form Control button names or create a list of worksheet button names and cell location. Problems and quirks with button names.

Excel Dashboards: Registration is now open for Excel TV's online course, Excel Dashboard Pro. Use coupon code contextures20 to get a 20% discount. Read my review of the course. Closes Feb. 7, 2017.

Learn Macros and VBA: Attend a free webinar by Jon Acampora, to learn the basics of Excel macros and VBA.

Excel Dashboards: What hashtag would you use to get help with an Excel Dashboard? Go to the blog post, and add your comment to enter the giveaway for Excel TV's new Excel Dashboard Pro course.

Pivot Tables: Show multiple Excel pivot table subtotals with custom subtotals

Grouped Dates: How to prevent grouped dates in Excel filters and pivot tables

Data Analysis: Export your food tracker data and analyze it with pivot tables in Excel

Highlight Cells: Use Excel conditional formatting to highlight cells, based on two conditions

VisiCalc History: See Dan Bricklin's and Mitch Kapor's tweets about the Software Arts (VisiCalc) anniversary.

Pivot Table Macro: Instead of manually removing pivot fields from a pivot table layout, use a macro to quickly remove them.

Dashboard Webinar: Attend one of Mynda Treacy's free Excel Dashboard webinars this week. Lots of great tips packed into a 1 hour presentation.

Get Organized: How to organize your Excel files, so you can find workbooks when you need them.

Excel Charts: How to make a Marimekko chart in Excel, either manually or with an add-in

Power Query and Data Cleansing: Free Power Query and Data Cleansing webinar presented by John and Oz at MyExcelOnline. Learn how to transform messy data and automate reports.

COUNTIF: Take the Excel COUNTIF challenge -- can you find the problem with this formula, and fix it?

Drop Down Lists: To prevent duplicates, hide used items in a data validation drop down list. The list only shows the remaining items, not the full list, to make data entry easier.

Pivot Table List: Use this macro to list all pivot tables in Excel file -- helps with troubleshooting in a complex workbook.

2016/10/27: Create an interactive Excel chart - select date range, choose criteria, pick value series.

2016/10/20: Show data validation error messages to help people enter data correctly. Or, turn error warnings off, to allow other entries in a cell.

2016/10/18: New items appear at end of Excel pivot table lists, not alphabetical. How to fix the problem

2016/10/17: Happy Spreadsheet Day 2016! What are you doing to celebrate?

2016/10/06: Create a timetable for a one-day project with this Excel task scheduler

2016/10/04: Show a distinct count (unique count) in a pivot table (Excel 2013 or later).

2016/09/29: Take a short break from spreadsheets, and join the Excel sing-along!

2016/09/20: How pivot table grand totals are created, and which grand total headings you can change.

2016/09/22: Instead of an in-cell drop down list, show a combo box with bigger font and autocomplete. Now works with simple dependent data validation too.

2016/09/15: If you're just getting started, follow these steps to plan and build a pivot table

2016/09/06: Create a pivot table instantly, then make simple changes to get a different view of the data.

2016/09/01: Double-click a pivot table value, to get a new sheet with the details. To prevent workbook clutter, use this Show Details Sheets macro to label those sheets, then delete them when closing the file

2016/08/18: Quick ways to unpivot Excel data, so you're ready to build a pivot table

2016/08/04: After a recent Microsoft update, some Excel add-ins are not loading correctly. To fix that problem, you can, follow the instructions here, to unblock the files. Read the background story on the blog.

2016/07/21: How to show an Excel table name on the worksheet, by using a special formula from AlexJ

2016/07/20: Use this macro to quickly get rid of "sum of" in Excel pivot table headings. For selected pivot table only, or all pivot tables.

2016/06/23: What weird things do you use Excel for? I made a garbage collection schedule

2016/07/07: Use a drop down list to show sheets based on their tab colour in Excel. Other sheets are hidden -- choose "(All)" to see all sheets again.

2016/06/16: Use a drop down list to show specific sheets in Excel. Other sheets are hidden -- choose "ALL" to see all sheets again.

2016/06/09: How to stop numbers from changing to dates in #Excel. Keep 3-2 or 5/6 as is, when pasting onto a worksheet

2016/06/02: Add favourite commands to Excel right-click menu with free add-in MenuRighter

2016/05/26: Do an Excel lookup based on 2 criteria -- get the product price based on item name and size

2016/05/19: Keep track of what's in a pivot table - macro lists all pivot fields and items, hidden or visible

2016/05/12: How to create a custom pivot table style, or make minor change to default styles

2016/05/10: Use a macro to remove calculated fields from an Excel pivot table, without seeing an error message.

2016/05/05: Enter complex Excel formulas fast with this AutoCorrect trick

2016/04/28: Use a pivot table running total to compare year to year sales in Excel

2016/04/21: Choose an option from an Excel drop down list, to control what data shows in the worksheet. For example, pick a language, to change date formatting codes.

2016/04/17: How to fix an Excel pivot table that is showing rounded times. Zeros appear, instead of tenths of a second or hundredths of a second.

2016/04/14: Create an Excel hyperlink with drag and drop. Drag to different sheet, or to nearby cell, then copy and paste to Table of Contents.

2016/04/11: New book by Excel MVP, Mike Alexander, Excel Power Pivot and Power Query For Dummies

2016/04/07: How to fix Excel pivot table time problems - zero rounding and incorrect totals.

2016/03/31: Use Slicers to select criteria for an Excel filter. Send the data to a different sheet, and use drop down lists to select fields for the output.

2016/03/24: Highlight latest numbers in long Excel list. Conditional formatting colors all matching cells, different color of last occurrence.

2016/03/21: Excel Roundup: Excel dashboards, data analysis, Power BI, INDEX/MATCH functions, and more tips

2016/03/17: Go to a specific sheet in an Excel workbook -- drop down list of sheet names, no macros.

2016/03/14: Excel Roundup: Excel reporting tips, free webinars, troubleshooting, and more tips

2016/03/10: List all Excel sheets with their used range address and size - helps with troubleshooting large workbooks.

2016/03/07: Excel Roundup: Power Map, Excel formula errors, cell references, and more tips

2016/03/03: Is your VLOOKUP formula returning the wrong results? This simple thing can cause errors that you might not notice.

2016/02/29: Excel Roundup: Create calendars, chart tips, TEXTJOIN, count unique, and more tips

2016/02/25: Use a pivot table to count unique items, e.g. in 100 orders how many different customers?

2016/02/22: Excel Roundup: Slicer trick, pivot charts, slow workbooks, dashboards, and more tips

2016/02/18: Quick ways to split combined date and time into separate columns

2016/02/15: Excel Roundup: Excel skills, make hyperlinks, dashboards, data analysis, and more tips

2016/02/11: Make an interactive Valentine card in Excel - uses data validation, conditional formatting, and hearts! (no macros)

2016/02/08: Excel Roundup: Find Excel help, new functions, conferences, sheet protection, and more tips

2016/02/04: In the Data Validation Multi-Select Kit, or other Excel code, make a small change to run faster in large workbooks.

2016/02/01: Excel Roundup: Dashboard charts, status bar messages, array formulas, and more tips

2016/01/28: How to fix Excel numbers that don't add up, with Paste Special - Add.

2016/01/25: Excel Roundup: Dashboard charts, status bar messages, array formulas, and more tips

2016/01/21: Show macro progress update messages in #Excel Status Bar.

2016/01/18: Excel Roundup: Random numbers, big data, floating forms, chart labels, and more tips

2016/01/14: Show Excel macro buttons on floating form -- move to any area of the worksheet.

2016/01/11: Excel Roundup: Number format trick, chart fix, spreadsheet comedy, and more tips

2016/01/07: Change Pivot Chart layout for columns in different colors

2016/01/04: Excel Roundup: Excel Calendar, Power Query, Check Box macro, and more tips

2015/12/17: Interactive Excel Christmas tree - no macros - formulas and conditional formatting.

2015/12/14: Excel Roundup: Bad dashboards, essential tricks, calculation speed, and more tips

2015/12/10: Calculate project end date with WORKDAY.INTL function, and set your own non-work days and holidays.

2015/12/07: Excel Roundup: Prevent errors, break links, Excel summit, Advent calendar, and more tips

2015/12/03: If you merge cells, Excel won't adjust the row height like it does for other cells. You can use a macro to fix the row height before you print, so all the text is visible.

2015/11/30: Excel Roundup: Prevent errors, break links, Excel summit, Advent calendar, and more tips

2015/11/19: It's the Contextures website's 15th anniversary. How old are your files? Get a list of Excel files with date the content was created.

2015/11/16: Excel Roundup: Rolling total, chart tricks, unmerge cells, clean data, and more tips

2015/11/12: Create a rolling total in Excel; show sum of previous 12 months in each row

2015/11/09: Excel Roundup: Hide results, find errors, lock slicers, vote for new Excel features, and more tips

2015/11/05: Hide formula results based on dates -- only show items from today or earlier

2015/11/02: Excel Roundup: Build a dashboard, Prevent duplicate totals, Power Query, formatting nit-picks, and more tips

2015/10/29: How to create Excel Subtotals and prevent duplicate grand totals

2015/10/26: Excel Roundup: Combine data, select multiple items, better Excel reports, and more tips

2015/10/22: Use a drop down list to enter multiple items in a cell

2015/10/19: Excel Roundup: Delete sheets, hide passwords, Boolean logic, and more tips

2015/10/15: Count items in one column, based on values in another column

2015/10/17: Happy Spreadsheet Day 2015! Vote for your favourite programs in the Spreadsheet Hall of Fame

2015/10/12: Excel Roundup: Option buttons, NPV function, LEGO design, extract comments, and more tips

2015/10/08: Adjust Option Button scores with Excel CHOOSE function

2015/10/05: Excel Roundup: Office 2016 guides, Spreadsheet Hall of Fame, filters, and more tips

2015/10/06: Microsoft Developer Resources links

2015/09/30: Happy 30th Anniversary Excel! Do these stories remind you of your earliest Excel memories?

2015/09/30: Jon Peltier has released Version 3.0 of his Excel Charting Utility. Works on both Windows and Mac.

2015/09/28: Excel Roundup: Excel history, Office 2016 reviews, chart tips, reduce errors, and more tips

2015/09/21: Excel Roundup: Bad charts, Office 2016, circular references, nested IFs, and more tips

2015/09/17: Beware of nested IF formulas in Excel

2015/09/14: Excel Roundup: Date calculations, Sum functions, seating plan, case sensitive lookups, and more tips

2015/09/10: 7 Ways to Sum in Excel. Quick grand totals, wildcards, and more

2015/08/31: Excel Roundup: Time entry, chart tricks, Power BI, pivot table survey, and more tips

2015/08/27: Use these navigation tips to find worksheets quickly

2015/08/26: Control Pivot Table top 10 filter settings with worksheet drop downs

2015/08/17: Excel Roundup: Time entry, chart tricks, Power BI, pivot table survey, and more tips

2015/08/13: Prevent invalid time entries with drop down lists of hours and minutes.

2015/08/05: Change Excel pivot table to Outline layout with VBA

2015/08/03: Excel Roundup: Survey results, dashboard tips, seating chart, and more tips

2015/07/30: Show a chart or its data on a dashboard, by choosing from a drop down list.

2015/07/20: Excel Roundup: Excel Dashboard webinar, pivot table trick, survey-love or hate Excel?

2015/07/16: Copy the numbers only, in a column with numbers, blank cells, and text.

2015/07/06: Excel Roundup: Bullet charts, dashboard export, pivot table rank + more Excel tips  Podcast

2015/07/02: Use the Rank calculation in an Excel pivot table, to make it easier to compare data.

2015/06/22: Excel Roundup: Weekly planner, apply exchange rate, table references, productivity tips + more

2015/06/18: Download the free Excel weekly meal planner - select meal items and print a shopping list. Functions calculate costs, with a pivot table and macro for the shopping list.

2015/06/15: Excel Roundup: Chart labels, mouse tricks, VBA intro, hyperlinks + more

2015/06/11: Compare Excel worksheet values and formulas with the INDIRECT function

2015/06/08: Excel Roundup: Dashboards, date formats, nested IFs, pivot layout + more

2015/06/04: Use the keyboard, or right-click menus, to move fields in a pivot table layout without dragging

2015/06/01: Excel Roundup: Flexible formulas, Power BI, interactive chart, invalid entries + more

2015/05/28:Fix Excel drop down list that allows invalid entries

2015/05/25: Excel Roundup: Reverse names, add chart shapes, Solver, new chart types, + more

2015/05/21: Short video shows 7 ways to count in Excel - numbers, data, criteria + more

2015/05/18: Excel Roundup: choose chart type, fast formulas, Power Map, + more

2015/05/13: Great Excel pivot tables need well-designed source data. These tips & macros will help.

2015/05/11: Excel Roundup: best file format, % Running Total, book sampler, + more

2015/05/07:Use Percent Running Total to analyze sales in a pivot table

2015/05/04: Excel Roundup: Fix Excel tables, save time with shortcuts, check formulas, Power BI, + more

2015/04/30: Fix an Excel table that doesn't expand automatically for new data

2015/04/20: Excel Roundup: Dashboard course, VLOOKUP, Data Vis, shortcuts, + more

2015/04/23: Upcoming free online events for Excel fans -- catch these before their deadlines!

2015/04/20: Excel Roundup: Dashboard course, array formulas, faster VBA, rounding, + more

2015/04/16: Excel automatically rounds numbers in General format, shows max of 11 characters

2015/04/13: Excel Roundup: Dashboard webinar, function tips, data analysis, slow VBA, VLOOKUP + more

2015/04/09: Track project tasks in Excel with estimated and actual times

2015/04/06: Excel Roundup: Column chart alternatives, random numbers, goal seek, complex counting + more

2015/04/02: Customize the context menus in Excel - add your favorite commands

2015/03/30: Excel Roundup: Project management, dashboard highlights, chart color, counting functions + more

2015/03/26: Learn 7 ways to count in Excel, with function tips and tricks

2015/03/23: Excel Roundup: Expense calculator, Power BI updates, keyboard shortcuts, remove colours + more

2015/03/19: Find and fix pivot table source data so new data shows up automatically

2015/03/16: Excel Roundup: Pivot Tables, Excel improvements, dynamic charts, highlight numbers + more

2015/03/12: Highlight numbers that are found in another list, with conditional formatting

2015/03/09: Excel Roundup: Power Query, average without outliers, data visualization, Excel 2016 for Mac + more

2015/03/05: Use Excel's TRIMMEAN function to calculate average that excludes outliers

2015/03/02: Excel Roundup: Dynamic charts, Excel tips, Power Pivot, Dashboard Design + more

2015/02/26: Resize Excel comments to a consistent width, with a macro

2015/02/23: Excel Roundup: Fix comments, create Gantt chart, combine files + more

2015/02/16: Excel Roundup: Data tips, Pivot table subtotals, free apps, Power Query + more

2015/02/12: Hide all pivot table subtotals, manually or with a macro

2015/02/09: Excel Roundup: Data cleanup, dashboard problems, combo charts, Power Query + more

2015/02/05: Save space with compact buttons and captions for macros or hyperlinks

2015/02/04: Create a combo column-line pivot chart in Excel 2013

2015/02/02: Excel Roundup: Pop-up calendar, cost benefit, pivot source data, fuzzy logic + more

2015/01/29: Football fun in Excel - find functions and compare close games

2015/01/28: Pros and cons of saving the Source Data with a Pivot Table file

2015/01/26: Excel Roundup: Combine data, sort with formulas, Power Query, business logic + more

2015/01/21: Create a pivot table from data on different sheets

2015/01/19: Excel Roundup: VLOOKUP, Power Query, pivot table makeover + more

2015/01/15: Delete worksheet rows based on Conditional Formatting color

2015/01/14: Give your Excel Pivot Table a makeover so the data is easier to read and understand

2015/01/12: Excel Roundup: sparklines, Power View, iPad formulas + more

2015/01/08: In Excel table, add counter field that fills in automatically

2015/01/07: Create a pivot table calculated field that uses a count

2015/01/05: Excel Roundup: 3-D charts, billing sheets, Day 42000 + more

2014/12/18: Count hotel guests in a date range, based on arrival and departure dates.

2014/12/15: Excel Roundup: Scroll bar Christmas tree, data analysis, moose hat + more

2014/12/11: Select multiple items from a long drop down list, using a popup form with combo box and list box.

2014/12/08: Excel Roundup: Christmas planner, nested IFs, in-cell charts + more

2014/12/05: This week's collection of Excel tweets, for your Friday entertainment.

2014/12/04: Create short drop down lists , with only items that contain specific letters

2014/12/01: Excel Roundup: data table, ignore blanks, combine sheets + more

2014/11/27: Change currency separators with Text to Columns feature

2014/11/26: Grouping dates creates extra items in pivot table filter

2014/11/24: Excel Roundup: data table, ignore blanks, combine sheets + more

2014/11/20: Ignore blank cells when copying and pasting on a worksheet

2014/11/19: Create a calculated field in a pivot table

2014/11/17: Excel Roundup: custom reports, VLOOKUP, pivot table fears + more

2014/11/13: Create custom report layouts with pivot tables and GetPivotData function

2014/11/10: Excel Roundup: VLOOKUP, Unselect tool, formula tips + more

2014/11/06: Fix numbers that don't add or sort correctly

2014/11/03: Excel Roundup: infographics, Monte Carlo, shortcuts + more

2014/10/30: Quick tip to create new workbook based on old one

2014/10/29: What scares you about pivot tables? Pivot table online course giveaway

2014/10/27: Excel Roundup: power hacks, color bands, running total + more

2014/10/23: Compare high and low scores in clustered stacked chart, with careful data arrangement

2014/10/22: Show running total in pivot table, by date, or any other field

2014/10/20: Excel Roundup: Spreadsheet mistakes, interactive chart, clean data + more

2014/10/16: Monitor multiple pivot table reports with diagnostic display

2014/10/15: Automatically refresh a pivot table

2014/10/13: Excel Roundup: Custom maps, interactive chart, formula tips + more

2014/10/09: Advanced Filter problem when running Excel 2013 macro

2014/10/08: Pivot table intro and resource links

2014/10/06: Excel Roundup: Pivot table grouping, Excel battery chart, dashboard tips + more

2014/10/02: Add your own icon to an Excel custom Ribbon tab

2014/10/01: Excel weekly meal planner with pivot table shopping list

2014/09/29: Excel Roundup: Dashboard tips, tab number trick & pivot table shoes + more

2014/09/25: Color data entry cells with conditional formatting, to make them easy to find

2014/09/24: Use GetPivotData function to pull amount from pivot table on specific sheet

2014/09/22: Excel Roundup: Pivot formatting, Excel auditing, rounding, jambalaya + more

2014/09/18: Problem with Excel 2013 worksheet combo box; won't use list directly based on table.

2014/09/17: Macro formats pivot table numbers based on source data formatting

2014/09/15: Excel Roundup: Animated charts, group by color, surprising shortcut + more

2014/09/11: Select from 1st drop down, macro clears dependent drop down cell, to prevent mismatches.

2014/09/08: Excel Roundup: Roman numerals, chart templates, event planning + more

2014/09/04:When testing VBA code, the F8 key didn't always stop at the next line; registry change seems to have fixed the problem.

2014/09/03: 5 Annoying Pivot Table Problems and how to avoid them

2014/08/25: Excel Roundup: Strange bugs, clean data, cool projects + more

2014/08/21: Create random text for sample data, with Excel functions RANDBETWEEN and CHOOSE.

2014/08/20: Remove "Sum of" from pivot table value field headings - video shows the steps.

2014/08/11: Excel Roundup: Pivot filters, 101 formulas, Power BI outline, change named ranges + more

2014/08/07: Use an Advanced Filter to find the top 5 orders, for products sold after a specific date. Show the results in place, or send the list to a different worksheet.

2014/08/06: Use a pivot table's Top 10 filter to show best-selling products. Then, change the filter, to see how many of the low selling products it takes to equal the sales of the top 3 products

2014/07/24: Use the REPT function to set a minimum row height. Makes it easier to read a list, or maintain row height for pictures.

2014/07/23: There is a new Pivot Table Tutorials index page to help you find articles, and I'm updating the Pivot Table FAQ list.

2014/07/10: Use this macro to automatically add new Items to drop down lists. The macro asks first, then updates and sorts the list

2014/07/09: Contextures PivotPower Premium Update - check your email for the download link, if you've bought this time-saving add-in

2014/07/08: Peltier Tech's new Advanced Charting Utility for Mac Excel was just released. NOTE: Windows version also available -- a real timesaver for Excel charts

2014/06/26: With this macro, enter a number, and it is added to all the selected cells

2014/06/19: For large, complex Excel files, there are helpful add-ins to make the job easier. Some are free, and others are paid.

2014/06/18: Show the fiscal year and month to date totals in a pivot table, when you add calculations in the source data.

2014/06/17: With a simple macro, you can automatically show or hide a worksheet, if a specific cell changes.

2014/06/15: Charles Williams has released a new version his add-in, FastExcel V3

2014/06/17: Book release -- PowerPivot Alchemy: Patterns and Techniques for Excel, by Bill Jelen and Rob Collie.

2014/06/12: Create a family tree in Excel, using Smart Art's organization chart, and VBA, with a sample file from Prof. Lee Townsend.

2014/06/11: Show fiscal year and fiscal month totals in a pivot table, by adding calculations in the source data.

2014/06/04: Learn how to turn off the GetPivotData formulas that create an absolute reference when you link to a pivot table.

2014/06/05: You can use shortcuts to quickly create a chart on a new sheet, or embedded on the active sheet.

2014/06/05: See quick tips for auditing Excel formulas, and doing some troubleshooting on a worksheet.

2014/05/28: Steps for copying and using sample pivot table code in your own workbooks.

2014/05/28: Create IFRAME code with formulas, and copy it to your web page or blog article, to embed a YouTube video, or other content.

2014/05/20: Roger Govier shared two ways to get a unique count in a regular pivot table.

2014/05/21: Learn how to group dates by week in a pivot table, and select a specific starting date

2014/05/21: Do some troubleshooting with the new SHEET and SHEETS functions in Excel 2013.

2014/05/08: Book Release -- Quantitative Finance: A Simulation-Based Introduction Using Excel, by Matt Davison.

Don't Miss Our Excel Tips

Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.