Excel tips and tutorials
The SUBTOTAL function is great for calculating totals on a filtered list in Excel. Unlike the SUM function, SUBTOTAL ignores the values in rows hidden by the filter, and can even ignore manually hidden rows, so the total includes only the visible cells.
Continue reading “Sum a Filtered List with AGGREGATE Function”
Earlier this week, I copied a list of Excel keyboard shortcuts from the Microsoft website, and pasted it into Excel. Then I noticed that a few objects had come along with the shortcuts list.
Pivot table filtering was improved in Excel 2010, when Slicers were introduced. Instead of using the drop down lists in the pivot table headings, you can click on a Slicer, to quickly filter the pivot table. You couldn’t use Slicers to filter a table in Excel 2010 though.
Continue reading “Use Slicers to Filter a Table in Excel 2010”
There is a new sample file on my website, in response to a lookup question that someone asked on my Contextures Facebook page. The sample file shows how to get mileage from Excel lookup table, when you pick two cities.
This workbook shows how to calculate annual costs and savings in Excel. It’s amazing how all those little expenses can add up over a year.
Continue reading “Calculate Annual Costs and Savings in Excel”
A few years ago, Ron Coderre created his PivotPlay PLUS Add-in that you can download from my Contextures site. This free add-in was designed for Excel 2003, and lets you view and edit the connection strings for pivot tables and query tables that are based on external data queries.
If you want to insert a block of cells on a worksheet, what method do you use most often? Do you insert rows with Excel fill handle, or with a command?
One of the new features in Excel 2013 is that each file opens in a separate window. Having each file in its own window makes it easier to compare files side-by-side, and most of the time I like the separate windows.
It’s been a bad week for Excel, with big news stories about spreadsheet errors. Two Harvard professors were in the news after a student at the University of Massachusetts found errors in their economic research paper.
Your challenge for today is to come up with a real-life use for this nifty (do the kids still say nifty?) Excel scrolling tool.
Continue reading “Click to Move Excel List Items Up or Down”
