Search Contextures Sites
Excel Weekly News from Contextures May 28, 2013
New AGGREGATE function + more Excel tips
If you're back at your desk after a long weekend, I hope you relaxed, and stayed away from the computer. Maybe you got out to the countryside, like the rustic location in my photo this week, at the end of the newsletter.
In this week's Excel news, you'll see how to total a filtered list with the new AGGREGATE function, change the report filter layout in a pivot table, and other tips. Thank you for reading the Excel news!
- Debra firstname.lastname@example.org
Total Filtered Data with AGGREGATE Function
If you're using Excel 2010 or Excel 2013, there is a new function -- AGGREGATE -- that is better than the old SUBTOTAL function.
It has 19 built-in functions, including MEDIAN, LARGE and SMALL, compared to the 11 functions in SUBTOTAL.
Also, it has options for what to ignore in the data, so you can ignore errors, as well as hidden data.
Click here to see the details and to watch the short video: Sum a Filtered List with AGGREGATE Function
Change the Report Filter Layout
Instead of showing the pivot table report filters in a single column, you can change a setting, to show the filters in multiple columns.
Click here to see the details, and to watch the video: Create a Simple Calculated Field
Did Excel Help Your Career?
Last week, someone emailed me with a lovely story about how Excel, and pivot tables, had made a tremendous difference in a young woman's career.
With their permission, I shared that story on my Contextures blog, and readers have added their own stories in the comments. I hope that the stories will inspire you too!
Click here to read the story: Did Excel Help Your Career?
More Excel Tips
Here are a few more Excel articles that I read this week, that you might find useful:
- In his Your Sum Buddy blog, Doug Glancy shows how he uses criteria tables, and formulas in the source data, to help with filtering a pivot table.
- You probably don't do too much artwork in Excel, but Mike Alexander shows how to remove backgrounds from images on a worksheet.
- For a peek at what other people are saying about Excel, read this week's collection of Excel tweets, on my Excel Theatre blog.
- Excel MVP, Jan Karel Pieterse, has updated his free Treeview control, which lets you show hierarchical data in a UserForm.
Video: Change Column Headings to Letters
When you open an Excel file, you might see numbers -- 1, 2, 3 -- across the column headings, instead of letters -- A, B, C. To see why this happens, and how to switch the column headings back to letters, please watch this short video tutorial.
If you'd like the written instructions, please visit my Contextures blog: Excel Column Headings Show Numbers
Needs a Few Repairs
Even though I live in a big city, there is farmland only a few minutes away. On one of our recent drives, we passed this run down barn and shed. The barn could use a coat of paint, and a few repairs, but it does give a rustic look to this farm property.
Recommended Excel Tools
In addition to all the free Excel tips and tutorials, there are other Excel tools that you can invest in. To learn more about the products listed below, click on the links to take a look at their features, and decide if they're right for you.
- Contextures PivotPower Premium Add-in
- Contextures 30 Excel Functions in 30 Days
- Excel Online Course
- Excel Charting Tools
- Excel Dashboard Kits
- Excel Project Management Templates
- Excel VBA School
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2014
All rights reserved.