Here is the latest news about Microsoft Excel, and updates on the Contextures website and blogs. Also see the Excel Events page.
Filter a List: Two ways to filter Excel data for items in a list: 1) exact matches only, 2) partial match in cell contents.
Pivot Table Problem: How to troubleshoot an Excel pivot table with missing pivot items
Excel Humour: Read my weekly collection of Excel tweets, to see what people are saying about our favourite spreadsheet. For example, "horror story...Excel stopped working".
Excel Events: For upcoming events, see the Excel Events page.
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.
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/08: Data Analysis: Learn data analysis and Excel dashboards in Chandoo's latest online course -- 50 Ways to Analyze Data. Video lessons and sample workbooks cover vital skills like data analysis, data science, visualization, modeling business problems and finding best solutions.
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/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/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.
For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.
Search Contextures Sites
Last updated: May 19, 2017 9:50 AM
Contextures RSS Feed