Search Contextures Sites
Excel Weekly News from Contextures Apr 16, 2012Remove duplicates in a list, change all pivot tables with a single click, show a chart or its data, with no macros, and other tips, in this week's Excel news from Contextures.- Debra firstname.lastname@example.org
Remove Duplicates in an Excel ListIn Excel 2003 and earlier versions, you can use an Advanced Filter to remove duplicates. With the Advanced Filter, you have the option of hiding duplicates, or creating a unique list in a new location.
In Excel 2007 and Excel 2010, there's a new command on the Ribbon to make it easier to remove duplicates from a list. Be careful with the new Remove Duplicates feature though - it permanently removes the duplicates.
You can read about the new Remove Duplicates feature, and watch the video to see how it works.
If you'd prefer to use the Advanced Filter in Excel 2007, watch this Advanced Filter video to see the steps:
Change Pivot Table Filter All Sheets or Active SheetTo save time, and make Excel tasks easier, you can use programming to change multiple pivot table filters with a single click.
On the Contextures Blog, you can download my latest sample file for changing pivot table fields has 3 variations on the "Change All Page Fields" code:
- Change any page field in a pivot table, and all matching page fields, on all sheets, are changed.
- Change any page field in a pivot table, and all matching page fields, on the active sheet only, are changed.
- Change a specific page field in a pivot table, and that page field, on the active sheet only, is changed.
Show Excel Chart or Data on Dashboard With No MacrosOn the Contextures Blog this week, you can learn a cool Excel trick, that will save some space on your dashboards.Select "Chart" or "Chart Data" from a drop down list, and you can show either an Excel chart or the chart's data on your dashboard sheet.
There are no macros in the workbook -- this tip uses named ranges and a linked picture. The video on the Contextures Blog shows you the simple steps.
Fitting curves to your data using least squaresOn his JKP Application Development Services website, Excel MVP Jan Karel Pieterse shows us how to fit measurements to a curve in Excel. Jan Karel lists some of the simpler functions that can be used in Excel.
If you want to fit a more complex function -- like y=exp(a.x).sin(x) + b -- Jan Karel provides a sample workbook that you can download. He also explains the steps for building the file, with screen shots that make it easier to understand.
Excel HumourOnce again, I read the Excel tweets, so you don't have to! Here are a few favourites from this week. You can read the rest of the tweet collection here.
- You should see the Excel workbook I made for work. Cut our monthly work down by 2 days at least. So complex but so effective. #ButNoPayRaise
- I have made an incredible spreadsheet. It has drop downs, filters, & coloured cells! I wonder if Facebook want to buy it for $10 million?
- Discovered the keyboard shortcut for switching sheets in an Excel workbook. My excitement about this seems a sad commentary on my life.
Recommended Excel Tools
Contextures Inc., Copyright ©2014
All rights reserved.