Search Contextures Sites
Excel Weekly News from Contextures May 07, 2013
Quickly add rows, insert current time + more tips
Finally! Spring is here, and the woods are full of beautiful wildflowers. You can see Ontario's official flower, the trillium, in my photo this week, at the end of the newsletter.
In this week's Excel news, you'll see how to use the Fill Handle to insert or delete rows, fix GetPivotData errors, and other tips. Thank you for reading the Excel news!
- Debra firstname.lastname@example.org
Insert Rows with Excel Fill Handle
In a worksheet, you can use the Insert command, to insert new rows. If you're looking for a quicker way to insert and delete rows, you can use the fill handle shortcut.
Click here to see the details and to watch the short video: Insert Cells with Fill Handle Shortcut
Counting Query Tables in Excel
On my website, there is a free add-in -- Pivot Play PLUS -- that lets you view and edit the connection information for pivot tables and queries, if they are based on external queries.
The Pivot Play add-in was built by Excel MVP, Ron Coderre, for use in Excel 2003. While I was checking to see if it could/should be updated, I found some changes in the way the Excel uses query tables now.
Click here to learn about the add-in, and to see the code for counting query tables on a worksheet. Counting Query Tables in Excel
GetPivotData Problems With Subtotals
The GetPivotData formula is great for pulling pivot table data, but can create an error if you use it with pivot table custom subtotal.
Click here to see the problem, and learn how to fix it: GetPivotData Problems With Subtotals
More Excel Tips
Here are a few more Excel articles that I read this week, that you might find useful:
- On the Microsoft website, the Excel team shows how to sum data for multiple criteria on multiple sheets.
- On his Bacon Bits blog, Mike Alexander shows how to build Waffle charts in Excel. They look nice, but take some effort!
- On his Get Digital Help blog, Oscar Cronquist explains how to build a calendar in Excel, and click on a date to see a list of events.
- The Science Goddess shares her interactive workbook that uses slicers to create a list of educational material.
- If you use statistics, take a look at Colin Legg's solution for creating a conditional average on non-contiguous ranges, using the FREQUENCY function.
Video: Insert Dates and Times With Formula or Shortcut
With a formula in Excel, you can add the current date and time to a worksheet, and it will automatically update.
If you want a date that doesn't change, use a keyboard short cut to lock in the time and date. This video shows you the steps for both solutions.
For more data entry tips, please visit my Contextures website: Enter Date and Time in Excel
Excel Dashboard Course Deadline
As I mentioned last week, Mynda Treacy from My Online training Hub is opening her Excel Dashboard course, for a limited time, and this Wednesday, May 9th, is the registration deadline.
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. The previous classes were very successful, and you can read the glowing reviews from the students, who loved all the techniques that they learned in the course, and are using them to impress their colleagues.
Click here to find out find out details of the course, read the student comments, and watch the 'behind the scenes' video that shows you what you'll receive as a member.
Remember, May 9th is the last day to register.
Recommended Excel Tools
They aren't free, but these Excel tools are a worthwhile investment, so please 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 the products mentioned in this newsletter, and earn a commission on the sales.
One of my favourite signs of spring is the trillium, with its bright white flower. The woods were full of them last week, and here's one of the prettiest ones for you to see.
Contextures Inc., Copyright ©2014
All rights reserved.