Contextures

Contextures News

Excel Numbers Change to Dates

June 7, 2016

Stop numbers such as 1-4 from changing to dates, and more, in this week's Excel news. If there are topics that you'd like to see covered in future newsletters, please let me know.

 -  Debra -  ddalgleish @ contextures.com
     Visit my Excel website for many more tutorials and videos.

Paste Numbers and Keep Their Formatting

Let's start with an easy fix for an annoying problem. If you copy numbers from a website, and try to paste them into Excel, things might not go exactly as you planned. For example, you copied a list of games scores, such as 3-2, 6-3, and 7-1, Excel might "help" you, by changing those numbers to dates. Grrrr!

To prevent Excel from re-formatting your perfectly good data, decide where you're going to paste it, and format that range as Text. Then, copy the data, and in Excel right-click the starting cell in your range. In the popup menu, under Paste Options, click Match Destination Formatting

You can get the detailed steps and screen shots on my website.

numbers change to dates

Formula Feedback

Now, on to something a little more complicated -- but not as scary as it looks! Last week, I showed you this formula to count cells that contained specific text: We wanted to count "JK", but not "JKA".

=SUM(IFERROR(SIGN(SEARCH(SelSep&$Z2&SelSep,SelSep&$A$1:$D$4&SelSep)),0))

You can see a detailed explanation of that formula on my website now, with screen shots that show how each function within the formula works.

Excel Versions

Thanks for answering my short survey about Excel versions! I created a summary of your answers, so take a look, to see what other people are using.

I've almost got my new laptop set up, and next week I'll share my list of all the settings you can change, before getting started with a new version of Excel. That might save you some time if you ever change computers or upgrade Excel.

Excel Articles

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

Power BI -- John Paul Cook shows how to get started with the free Power BI Desktop tool, so you can create charts from data files that are too big for Excel. As he says, "You don�t have to be a geek or a power user to succeed with Power BI". (Level - Intermediate/Advanced)

Excel Courses -- Here's another great (and free!) Excel-related online course coming up on the edX site. It is Data Analysis: Building Your Own Business Dashboard and it starts on June 22nd. (Level - Intermediate/Advanced)

More Excel Articles -- Visit my Excel website for many more tutorials and videos. And for a bit of fun, read my weekly collection of entertaining Excel tweets.

Mushy Bananas

I don't do too much baking these days, but when you have a few really ripe, mushy bananas, it's hard to resist making a loaf of banana bread. To ease my conscience, I cut back on the sugar, and added some wheat bran. A nutritionist might say that didn't make it any healthier, but it sure smelled good!

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:

www.contextures.com/newsletter/excelnews2016/20160607ctx.html

Recommended Excel Products

  • Easy Data Entry: Add a popup list, to your worbook, so you can quickly enter items in a cell. The Data Validation Multi Select Premium (DVMSP) kit helps you set things up. Then, a list appears when you click a cell, so only valid items can be entered. Reduce data entry errors and typos. You can customize the list, to allow single or multiple selection, in specific columns.
  • Excel Chart Tools: If you build complex charts, save time with Jon Peltier's Excel Charting add-in. Don't waste hours trying to format the chart yourself!

___________

Choose Your Excel News: Get the full Contextures news package, or the basic one. To change, click a link below, and use the sign-up form on that page.

  • Full News Package: Weekly newsletter, plus occasional emails so you won't miss out on special Excel deals, free webinars, new products, and other Excel news.

Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.

 

 

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: June 14, 2017 11:49 PM