Contextures News 20150721

Do you hate Excel?

July 21, 2015

Free dashboard webinar, quick Excel survey, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Summer Schedule

During the summer months I'll publish the newsletter once every two weeks, so you will get the next issue on August 4th.

Copy and Paste Numbers Only

When you're copying and pasting on a worksheet, use the built-in Excel tools to select specific things. For example, if a column has numbers and some blank cells, use the Go To Constants command to select all the numbers, and ignore the blanks.

To find that command, click the Home tab on the Excel ribbon, then click Find & Select, and click Constants

You can fine-tune the selection even more by click the Go To Special command in Find & Select. That opens up a window where you can specify the type of constants you want to select, such as Numbers, but not Text, Logicals or Errors.

To see the steps, you can watch the short video on my website. Download the sample file so you can follow along.

Show the Month Name

If there is a date in a worksheet cell, you can use the MONTH function to pull out the month number. If you want to show the month name instead, you can use this TEXT function tip from Anne Walsh's Data Cleansing Tip Sheet ($1)

• Select the cell where you want to show the month name
• Type the TEXT formula, with a reference to the date cell
• Use "mmmm" to show the full month name, or "mmm" for the 3-letter month name

For example, with a date in cell A2:

=TEXT(A2,"mmmm")

You can use the TEXT function for other types of number formatting too, and there are more examples on my website.

Excel Articles

Here are a couple of Excel articles I read recently, that you might find interesting and useful.

Pivot Table Trick -- Bill Jelen shows how to make an AutoFilter work on a pivot table, so you can use its features, such as filter for the top 5 values. Remember, if the grand total or subtotals are showing, they'll be included in the results. You could hide them before filtering. (Level - Basic)

Productivity Tips -- Chandoo shared 25 shortcuts and tricks to boost productivity, and #22 is one that I use frequently – press the F4 key to repeat the last action. It doesn't work for everything, but it's a real timesaver!

More Excel Articles -- Find links to more articles in the latest Excel Weekly Roundup on my Contextures blog.

Do You Love Your Version of Excel?

In the latest collection of Excel tweets on my Excel Theatre blog, someone said, "We just got upgraded to Excel 2013 at work and I hate everything."

What do you think about the version of Excel that you're using at work? Click here to answer my 3-question Excel Version survey, and I'll share the results in the next newsletter. Thanks!

Excel Announcements

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 July 30th. The course is a great investment, and you can read my review for the highlights, and more information.

Free Dashboard Webinars: Mynda is also offering two free one-hour webinars -- 1) How to Build Excel Dashboards, and 2) Dashboards with Power Query and Power Pivot. Get the details, and sign up for a date and time that is convenient for you.

Power Query Training -- The August 12th class has sold out, so Power Query experts Ken Puls and Miguel Escobar have opened registration for another August session of their online Power Query course. There are 2 days of live online training (4 hours per day). Then attend a live online follow-up Q&A session, a week later. The class is very popular, so register early!

In the Garden

So far, the summer weather has been great -- slightly cooler than normal, and just enough rain to keep the garden thriving. The perennials are starting to bloom, and this lovely purple flower is on a vine that grows over the fence from the neighbours' yard. It's nice when other people do the work, and we get to enjoy the rewards!

NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy this link and paste it into your browser: https://www.contextures.com/newsletter/excelnews2015/20150721ctx.html

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish
ddalgleish @contextures.com

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.