Home > Skills > FAQs > What's New What's New in Microsoft ExcelOn this page, you can see what's new, here on the Contextures website, and on each of my blogs. There's one of my recent videos too, showing the steps for an Excel feature. Also, there are links that take you to the Microsoft site, so you can find out what's new in Microsoft Excel, and the other Microsoft Office apps. For many more tutorials, go to the Excel Tips page. You'll find an Excel topic list there, with links to the tutorial pages. |
These are the latest articles that I've posted here on my Contextures site, as well as each of my blogs. There is a longer list of articles, further down on this page - it shows articles from previous months.
Contextures Blog: How to block duplicate entries in an Excel column, using custom data validation rules. Sep 21, 2023
SEQUENCE Function: Create quick number sequences with SEQUENCE function in Excel 365. Sept 13, 2023
Pivot Table Blog: Make it easy to compare sales data -- show items with no data, so layout stays the same. Sept 14, 2023
To see the latest features and fixes in Excel, go to these pages on the Microsoft site and YouTube.
Microsoft 365 Apps: For the Monthly Enterprise Channel, go to this page for a list of new features and fixes in the latest versions.
Microsoft 365: To see new features demonstrated, go to the Microsoft 365 YouTube Channel. Look for the monthly videos on "What's New".
Excel Improvements: Excel users are encouraged to post improvement suggestions on Microsoft's Excel UserVoice website. To see the completed items from that list, go to this filtered list. There's also a list in this Excel Tech Community article, which is frequently updated.
Office Updates: For a list of all Office updates, going back to 2010, and other Microsoft resources, visit the Office Updates page. There is a Download PDF link, at the bottom left corner on that page.
Watch this short video to see how to compare two Excel lists, to find new items in the second list, and add them to the first list
See the details, and get the sample workbook on the Excel Compare Two Lists page.
This section shows a list of previous articles that I've posted here on my Contextures site, as well as each of my blogs.
Contextures Blog: Open extra Excel window in large workbook. Warning: Close original window last! Aug 31, 2023
Pivot Table Blog: If Excel pivot tables have empty value cells, show a zero, or text string, instead. Aug 17, 2023
Contextures Blog: Don't let a merged cell eat your data in Excel! Stay safe, and use these tips instead. Aug 10, 2023
Contextures Blog: Keep Excel footer and header font the right size when printing, and add cell info with a custom footer macro. July 27, 2023
Pivot Table Blog: How to show pivot table subtotals at the top or bottom of a group -- with limitations! July 20, 2023
Pivot Table Blog: How to copy custom Pivot Table styles to another workbook in Excel. Video shows the steps. July 5, 2023
Contextures Blog: What's happening in spreadsheet news? Get #Excel data in EVE online, scroll bars in merged cells, #spreadsheet life! June 29, 2023
Contextures Blog: Compare two Excel lists, find new items, add those to main list. Video shows the steps. June 15, 2023
Merge or Combine: Should you merge cells in Excel, or combine cell values? Pros and cons, video, written steps. June 8, 2023
Pivot Table Blog: Quick fix for pivot chart with columns that are all the same colour. Video shows the steps. June 7, 2023
Contextures Blog: What's your favourite way to troubleshoot Excel formulas? Video shows new Excel features coming soon! June 1, 2023
Logical Functions: Short guide to Excel logical functions, including IF, IFS, IFERROR, AND, OR, XOR and more. May 22, 2023
Pivot Table Blog: Show or hide drop down arrows in pivot table headings, with option setting or with Excel macros. May 24, 2023
Contextures Blog: Use wildcards in Excel Find and Replace to save time in a long list. May 18, 2023
Pivot Table Blog: Use a "counter" field in the source data to get correct results with an Excel pivot table calculated field. May 10, 2023
Contextures Blog: Print "Guess the Word" game cards in Excel, for offline fun! May 4, 2023
Pivot Table Blog: Don't get stuck in a pivot table rut! Try different layouts and styles. Apr 12, 2023
Printable Calendar: Type year and month number, to create a printable Excel calendar with the month's holidays highlighted. Apr 5, 2023
Combo Box Macros: Click a macro button to quickly scroll through Excel combo box items. Formulas show selected day's totals. Mar 30, 2023
Pivot Table Blog: Do you love Excel GetPivotData function or do you turn it off? Mar 29, 2023
Drop Downs: See 3 types of Excel drop down lists for data entry -- Data validation, Form Control combo box, ActiveX combo box, with their benefits and limitations. Mar 22, 2023
Pivot Table Blog: Instead of complex Excel formulas, use a pivot table to show MIN IF or MAX IF numbers. Mar 15, 2023
Food Sales Data: Excel sample data, sales orders for fictional food production company. Use for testing, training, Excel skills practice. Mar 13, 2023
Debra D's Blog: Enter start and end dates on a Microsoft Access form, to use as date range criteria for a query. Mar 13, 2023
Contextures Blog: How to turn off the grey "Page 1" on your Excel worksheet. Mar 9, 2023
Unpivot with Power Query: Unpivot Excel data using Power Query, to create normalized column layout. Step-by-step guide, sample file. Mar 8, 2023
Pivot Table Blog: How to fix Excel pivot table subtotal date format to match other dates. Mar 8, 2023
Contextures Blog: Calculate ratios in Excel with this 3-step formula, and don't trust the bots! Mar 2, 2023
Pivot Table Blog: Move Excel pivot table values for better printing, with a vertical layout. Mar 1, 2023
Filtered Rows: Count or sum filtered rows in Excel list. How many rows are visible in Excel table? What the total for numbers in filtered table. Feb 24, 2023
Contextures Blog: Use an Excel macro to quickly increase amounts in multiple cells. Feb 23, 2023
Pivot Table Blog: Select specific pivot table section, such as subtotals, so you can apply formatting. Video shows the steps. Feb 22, 2023
Contextures Blog: Macros list all threaded comments on an Excel worksheet. You can show all replies for each comment too! Feb 16, 2023
Pivot Table Blog: Add fake clickable hyperlinks to Excel pivot tables. Video shows the steps. Feb 15, 2023
Debra D Blog: Are you ready to pivot? Yes, Bing Search Chat will change things, but if four people can turn a train, you can pivot your blog, to keep it growing!. Feb 13, 2023
Pivot Table Blog: Troubleshoot the problem for an Excel pivot table not showing all data. Feb 8, 2023
Contextures Blog:Repeat text with Excel REPT function for in-cell charts, table row height & more. Feb 2, 2023
Pivot Table Blog: Show extra subtotals in pivot table, without adding columns. Watch for limitations though!. Jan 25, 2023
Debra D Blog: This Ode to Microsoft Excel, for Robert Burns Day, gives humorous "burns" to other spreadsheets. Jan 25, 2023
Contextures Blog: Can ChatGPT write Excel TEXTJOIN formulas? See my test results. Jan 19, 2023
Combine Text: Combine text and numbers from multiple cells with Excel TEXTJOIN function. 7 examples, basic to advanced Jan 18, 2023
Fix Text: Fix Text with Excel SUBSTITUTE and REPLACE Functions. Jan 16, 2023
Pivot Table Blog: Pivot table troubleshooting - see quick pop-up message with details. Jan 10, 2023
Excel News 2022To see the list of articles for 2022, click on the green check box below. When you're finished, you can click the check box again, to hide the list Play Games in Excel: When your work is done, enjoy playing games in Excel! Do you remember the old hidden games in Excel? Dec 14, 2022 XLOOKUP Function: Basic examples show how to get started with Excel XLOOKUP function, in Excel 365 and Excel 2021. Dec 7, 2022 Pivot Table Blog: How to fix pivot table headings with "(blank)" or remove "Sum of" in headings Dec 7, 2022 Contextures Blog: Use Excel macros to quickly create 12 monthly workbooks, with daily sheets, for the upcoming year. Dec 1, 2022 Contextures Blog: Excel for the holidays - planners, Advent calendars, Christmas trees, personalized messages. Nov 17, 2022 Skip Blanks: Avoid pasting blank cells over existing data with Excel Skip Blanks feature. Nov 9, 2022 Pivot Table Blog: Is it hotter than usual this November? Compare years with pivot table conditional formatting colour scale Nov 9, 2022 Contextures Blog: Have you ever sent Microsoft a smile or frown in Excel? Go to the Help tab, and click Feedback. Nov 3, 2022 Add Sheets Macro: Macro adds new worksheet automatically when file opens at start of month. Nov 1, 2022 UserForm Search: Use this Excel UserForm to search transaction records in a worksheet table. Add new records, or find existing records, and view, edit or delete them. Sept 28, 2022 Contextures Blog: Before you click Remove All button for Excel Subtotal feature, be prepared -- there's no Undo! Sept 22, 2022 Count Blanks: In a pivot table, see how to count blanks in source data cells. Fix problem, get correct count of empty cells. Video, written steps, Excel workbook. Sept 13, 2022 Contextures Blog: How I fixed an Excel error message -- ⚠️ Reference isn't valid. Sept 8, 2022 Contextures Blog: Problems pasting into filtered Excel list? This shortcut might help, or try a workaround. Aug 25, 2022 Contextures Blog: It's football season! New Excel sample data for testing/training - NFL player rosters - 2 teams, 2 years . Aug 11, 2022 Pivot Table Blog: Date filters in Excel pivot tables - try these 3 different types. Aug 3, 2022 Contextures Blog: Video shows how to use wildcards with criteria for a flexible AVERAGEIF formula in Excel . July 28, 2022 Date Grouping: See how to turn off automatic date grouping for Excel Pivot Tables and AutoFilters. July 18, 2022 Contextures Blog: Track your golf scores in Excel, for multiple players & courses . July 14, 2022 Line Breaks: How to add line breaks in Excel cells and formulas. Find and remove line breaks. July 13, 2022 Pivot Table Blog: Quickly check sales progress with % Running Total feature in Excel pivot table - 2 videos show the steps. July 6, 2022 Contextures Blog: How to make country flags in Excel 365, with 1 formula instead of 48672! June 30, 2022 Compare Lists: Compare two Excel lists, to find new items in second list, and add them to first list. June 20, 2022 Pivot Table Blog: Quick and easy steps to make a clustered stacked pivot chart in #Excel. Video and sample file. June 8, 2022 Contextures Blog: How to find product price with Excel VLOOKUP and MATCH, for quantity ordered. June 16, 2022 Status Bar Tips: Use Excel Status Bar info to troubleshoot problems, and get key information. For macros, show custom messages with macro progress updates. June 8, 2022 Debra D Blog: Use bookmarks and shortcut keys for quick navigation in Notepad++ file. June 6, 2022 Contextures Blog: How to count duplicate number sets with Excel formulas, using Excel 365 spill functions. June 2, 2022 Debra D Blog: How to fix mouse double click problem in Microsoft Access, get it working again. May 30, 2022 Contextures Blog: How to show hidden data in Excel chart or Excel sparklines. Change chart setting manually or with macro. May 19, 2022 Basic UserForm - Videos: Step-by-step videos show how to make basic Excel UserForm with text boxes for data entry. May 16, 2022 Pivot Table Blog: Why does pivot table show duplicate numbers? How to fix the problem. May 11, 2022 FILTER Function Reports: See how to create multi-column summary reports, using Excel FILTER Function (Excel 365). May 7, 2022 Contextures Blog: Take a break from your work, and use Excel to stream web radio. May 5, 2022 Debra D Blog: How to fix avi format videos that have audio and black screen, with Video Editor in Microsoft Photos program. Apr 24, 2022 Contextures Blog: See warning if Excel worksheet sheet has hidden rows or columns, and steps show how to fix them. Apr 21, 2022 Compare Cells: How to compare cell values in Excel. Find exact match, or partial match and percentage. Apr 12, 2022 Pivot Table Blog: Help Excel pivot table macros run faster by preventing automatic updates. Apr 6, 2022 Excel Charts: How to show hidden data in Excel chart or Excel sparklines. Change chart setting manually or with macro. Mar 15, 2022 Highlight Weekends: How to highlight weekend data in Excel pivot table with conditional formatting. Fix rule settings to avoid problems. Mar 2, 2022 Contextures Blog: Quick Excel trick to move through data entry cells spread out on worksheet. Feb 24, 2022 Contextures Blog: New Excel sample data for you to use for testing or training - winter athlete data. Feb 10, 2022 Pivot Table Blog: Use this Excel dashboard to check all pivot table filters before sending reports. Feb 2, 2022 Debra D Blog: With Large Text (55% +) setting on iPad, press Date or Time for pop-up zoom. Jan 31, 2022 Excel Printing: How to create an Excel report diagnostic display to check filter settings before printing. Jan 27, 2022 Working Days: How to calculate project dates or upcoming workdays in Excel with WORKDAY function and WORKDAY.INTL function. Jan 17, 2022 Debra D Blog: How to Check Email Internet Headers in Microsoft Outlook, without opening the message. Jan 16, 2022 Contextures Blog: Have fun with a Word Cloud in Excel. Jan 13, 2022 Pivot Table Blog: Stop automatic GetPivotData formulas when you click on an Excel pivot table cell. Jan 12, 2022 |
Excel News 2021To see the list of articles for 2021, click on the green check box below. When you're finished, you can click the check box again, to hide the list Copy & Paste Problems: How to fix Excel copy & paste problems for multiple selections error, or formulas changed to values. Dec 27, 2021 Contextures Blog: Compare sales numbers with Excel pivot table's % Running Total calculation. How many top products make up 60% of total sales? Dec 9, 2021 Contextures Blog: How to fix an Excel table that doesn't expand automatically for new data. Nov 25, 2021 Contextures Blog: Send Excel data to different sheets, based on criteria, no macros! Nov 11, 2021 Contextures Blog: Choose random names from an Excel list. Or pick scary movies to watch this Halloween weekend! Formulas, no macros. Oct 28, 2021 Spreadsheet Day: This year's Spreadsheet Day is Sunday Oct 17th - are you ready to celebrate? Cluster Stack Pivot Chart: See how to create a Cluster Stack Pivot Chart from a pivot table. Use named Excel table or see how to unpivot data with Power Query. Sept 27, 2021 Remove Duplicates: Simple steps to remove duplicate items from Excel list. Fix problem when duplicates numbers not all removed. Sept 16, 2021 Contextures Blog: Three ways to create a clustered stacked chart in Excel. Sept 16, 2021 Conditional Formatting: Fix Excel conditional formatting problems when extra rules are automatically created. Manual or macro clean up. Sept 9, 2021 Contextures Blog: Choose golf tee off time and assign players in Excel. Partially filled time slots can be completed later. Sept 2, 2021 Spreadsheet Day Blog: Charts, orcas, pivot tables and more, in the latest spreadsheet roundup. Aug 31, 2021 Contextures Blog: Hide used items in Excel 365 drop down list, to prevent duplicate entries. Aug 26, 2021 Contextures Blog: Diving into the new Power Query book, and Excel Query pane problems. Aug 5, 2021 Pivot Table Blog: Save time-stamped backup copies of Excel files while you work with free add-in. August 4, 2021 Contextures Blog: Find MAX and MIN with criteria using Excel formulas or a pivot table July 22, 2021 Pivot Table Blog: Select value cells in pivot table, and run this macro to quickly remove them all. July 7, 2021 Date Range: How to sum or count in Excel, based on a date range, and other criteria. May 26, 2021 Contextures Blog: Here's an Excel roundup of articles on dashboards, tools, functions, and more, to get summer off to a great start! June 24, 2021 Pivot Table Blog: This Excel macro filters and prints a pivot table for each item in the Report Filter. It's a quick way to create printed reports. June 2, 2021 Contextures Blog: Why do Excel formulas stop working, and how can you fix them? June 10, 2021 Contextures Blog: Add letter headings in Excel drop down, so it's easy to find items in long list. Optional macros add/remove headings. Jan 21, 2021 PowerPoint Slides: Macro quickly creates PowerPoint slides from Excel list. Fill 1 or 2 text boxes in each slide. Use all list items or specific items, based on criteria. Jan 20, 2021 Pivot Table Blog: For visual impact in an Excel pivot table, use conditional formatting data bars. Jan 13, 2020 Drop Downs: Add single-letter headings in Excel drop down, so it's easier to find items in long list. Use data validation and optional macros to add and remove headings Jan 14, 2021 Contextures Blog: Here's a quick Excel tip from Sarah, for dividing numbers without using a formula. Video and written steps. Jan 7, 2021 |
Excel News 2020To see the list of articles for 2020, click on the green check box below. When you're finished, you can click the check box again, to hide the list Audit Excel Formulas: On copy of active sheet, macro colour codes formula cells that have been copied across, down, or both. Spot problems and fix them. Dec 16, 2020 Pivot Table Blog: How to fix an Excel pivot table sorting problem, when the wrong item is stuck at the top of the list. Dec 16, 2020 Contextures Blog: In an Excel Order Form, click the check box and a macro fills in the billing address. Clear the check box to clear address info. Sept 17, 2020 FILTER Function: Simple Excel FILTER function examples - how to create lists that update automatically. Set rules, format list items. Sept 15, 2020 Pivot Table Blog: Quickly refresh all pivot caches in workbook with #Excel macro. Message shows cache and failure counts. Sept 9, 2020 Contextures Blog: Use the new Excel TEXTJOIN function to combine several text items, quickly and easily. See simple and complex examples. Sept 3, 2020 Workbook Macro: At end of work day, use this macro to list and close all open Excel files. Next day, click button to open those files again. Like Excel's old Workspace feature. August 27, 2020 Criteria List: Filter an Excel table based on criteria lists on different sheet. Click buttons to quickly run macros that apply AutoFilter, or clear all filters. Get the free workbook. August 24, 2020 Contextures Blog: Click a Slicer to quickly show value groups in Excel pivot table. Great for source data with lots of value fields. August 20, 2020 Drop Down Lists: Limit choices with dependent drop down lists, based on choice in other drop downs. Select region, then sales rep, then customer for region and rep. Free Excel workbook. August 14, 2020 Pivot Table Blog: Click a Slicer to quickly show value groups in an Excel pivot table. Great for source data with lots of value fields. August 12, 2020 Contextures Blog: Clear old items from pivot table drop downs, and change the default settings for new pivot tables. August 6, 2020 Gantt Chart: Easy steps to create simple Gantt chart in Excel for project planning. Type task names and duration, chart shows timeline. Video, written steps, free workbook. July 31, 2020 Matching Items: Pick a region namefrom a drop down list, and Excel formulas show all the employee names from the selected region. No macros! July 23, 2020 Pivot Tables: Build 2 pivot tables on one Excel sheet, from the same source data. Then add a Slicer to quickly filter both pivot tables. July 22, 2020 Multiplication: Memorize times tables with this Excel practice workbook. Highlight errors, choose number ranges. July 9, 2020 Right-Click: This free Excel add-in, from AlexJ, puts Filter commands at top of right-click menus, so they're easy to use. May 28, 2020 Fiscal Months: Sort dates by fiscal month in an Excel pivot table, with month names included, for clarity. May 20, 2020 Spreadsheets: Dashboard tips, free spreadsheet courses and more, in this spreadsheet roundup.. May 22, 2020 Formulas: See 2 easy ways to copy Excel formulas with table references and avoid the problems that can cause incorrect results. May 21, 2020 Table Shading: Create colour bands in an Excel table, based on dates or other groupings. May 7, 2020 Mouse Shortcut: Use this handy mouse shortcut to quickly show and hide Excel pivot table details. May 6, 2020 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 |
Excel News 2019To see the list of articles for 2019, click on the green check box below. When you're finished, you can click the check box again, to hide the list 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? |
Excel News 2018To see the list of articles for 2018, click on the green check box below. When you're finished, you can click the check box again, to hide the list 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 and 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 News 2017To see the list of articles for 2017, click on the green check box below. When you're finished, you can click the check box again, to hide 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. |
Excel News 2016To see the list of articles for 2016, click on the green check box below. When you're finished, you can click the check box again, to hide the list 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 |
Excel News 2015To see the list of articles for 2015, click on the green check box below. When you're finished, you can click the check box again, to hide the list 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 and 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 |
Excel News 2014To see the list of articles for 2014, click on the green check box below. When you're finished, you can click the check box again, to hide the list 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 and 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. |
Last updated: September 21, 2023 9:54 AM