Search Contextures Sites
Add Count Automatically + more Excel tips
Excel Weekly News from Contextures January 13, 2015
In this week's Excel news, you'll see how to automatically enter a count field in a table, and much more. Thank you for reading the Excel news, and I hope that you are enjoying the articles. If you have suggestions for future topics, please let me know.
- Debra - firstname.lastname@example.org
- Add a Counter Field to Excel Table
- Calculated Field With a Count
- More Excel Tips
- Excel Announcements
- Video: SUBSTITUTE Function
- Weekly Photo: Silly Gadget
- Featured Excel Product
In a named Excel table, you can add a counter field that fills in automatically. This is can be used in summary functions, or added to a pivot table, even in calculated fields, like the one mentioned below.
Click here to see the details: Add a Counter Field to Excel Table
When you create a calculated field in a pivot table, you can refer to other value fields. However, the calculated field always uses the SUM of those fields, even if you have them displayed as a COUNT.
To fix the problem, you can add a counter field in your source data, and its sum will act as a count.
See the details in my blog post, and download the sample file: Create Calculated Field With a Count
Here are a few of the Excel articles that I read recently, that you might find useful:
- On the Microsoft Office blog, students can learn how to build a GPA calculator in Excel. Kevin Lehrbass spotted an error in the VLOOKUP formula, and it's fixed now.
- Also for Excel for iPad, Stephen Ashby shows how to create formulas in that version.
- Max Fraudy explains how to dynamically highlight a data point on your chart, without using VBA.
- Sumit Bansal shares a trick for using a defined name to get a list of file names from a folder in Excel.
- This mini-course from Microsoft's Office Online shows how to use the Sparklines -- tiny charts to show data trends.
- Leon Lovett shows how to stop Excel from formatting two hyphenated numbers to a date, when you import data.
- Dr. Nitin Paranjape demonstrates why it's easier to create a set of charts with Power View, instead of using regular Pivot Charts
- Finally, for a humorous peek at what other people are saying about spreadsheets, read the latest collection of Excel tweets, on my Excel Theatre blog.
Here are some upcoming events, courses, recently published books, and other new items, related to Excel:
- Amsterdam Excel Summit: Mark your calendar for April 13-15, so you can attend this amazing Excel event. Last year's summit was an outstanding success, and registration will open soon, for this year's event.
- Excel Dashboard Course: Mynda Treacy has opened registration for her acclaimed Excel Dashboard course, and you'll get 20% off, if you sign up by January 22nd. See details in the Featured Excel Product section, below.
- Mynda is also offering a free introductory webinar on How to Build Excel Dashboards. Get the details on this one-hour session, and sign up for a date and time that is convenient for you.
The SUBSTITUTE function replaces characters within text, either for all instances, or for one specified occurrence. It can be used on its own, and I often combine it with other functions, to create powerful formulas.
Click here to watch the short video that show how it works: SUBSTITUTE Function Video.
We got a new gadget for our kitchen, and it's useful, but one of the silliest things that I've seen. His name is Spud Dude, and his spikes hold a potato, while you peel it. I've used it a couple of times, and it keeps my fingers safe, and makes me smile too!
And if you're from Canada, it might remind you of the Stompin' Tom Connors song, Bud the Spud. (YouTube link)
The start of a new year is a great time to add some new tricks to your Excel repertoire. Stay inside, where it's cozy and warm, and follow an online course at your own pace.
Excel dashboard skills are certainly in demand in many companies, and Mynda Treacy from My Online training Hub is temporarily opening registration for her comprehensive Excel Dashboard Course. I've been through this course, and highly recommend it.
Mynda's dashboard course is a great investment, and if you sign up by January 22nd, you can get the course for 20% off. Registration closes on January 29th.
The course is video based, delivered online and is available 24/7. You'll receive comprehensive workbooks and sample dashboards to keep, and there’s even an option to download the videos.
See more recommended Excel products.
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2015
All rights reserved.