Here is the latest news about Microsoft Excel, and updates on the Contextures website and blogs.
UserForms: Get the latest version of my Excel Search Form Builder, now with 4 search boxes
Slicers: How to connect Excel Slicers to multiple Pivot Tables, then filter all the pivot tables at once
Golf Tee Off: In Excel, drop down lists and macro buttons make it easy to assign players to golf tee off times.
Excel Milestone: It's day 44000 in Excel's date system today (June 18, 2020)! Do we get the day off?
Sales Analysis: Analyze monthly sales with % Running Total calculations in Excel pivot table.
Right-Click: This free Excel add-in, from AlexJ, puts Filter commands at top of right-click menus, so they're easy to use
Fiscal Months: Sort dates by fiscal month in an Excel pivot table, with month names included, for clarity
Spreadsheets: Dashboard tips, free spreadsheet courses and more, in this spreadsheet roundup.
Formulas: See 2 easy ways to copy Excel formulas with table references and avoid the problems that can cause incorrect results
Table Shading: Create colour bands in an Excel table, based on dates or other groupings
Mouse Shortcut: Use this handy mouse shortcut to quickly show and hide Excel pivot table details.
Formula Challenge: What's the best Excel formula to sum for odd and even weeks? See all the solutions, and pick your favourite, or post your own formula in the comments
Easter Egg: Does "Pivot!" make you think of Ross from Friends? Have you seen this Google search Easter egg?
FastExcel: There's a new, improved version of FastExcel -- it helps you create smaller, faster #Excel files, and pinpoint problems.
Meal Planner: Use this free Excel weekly meal planner to keep organized while you #stayhome
Pivot Items: Use this Excel macro to keep one pivot item collapsed, but allow other items to expand
Excel Error: Have you ever seen this Excel pivot table refresh error, with Data Model data?
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?
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.
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.
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/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.
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.
Last updated: June 30, 2020 2:12 PM
Contextures RSS Feed