Is Your Workbook Safe?
Feb 9, 2016
Split dates and times, worksheet protection, and more, in this week's Excel news. Visit my Excel website for many more tutorials and videos.
- Debra - ddalgleish @ contextures.com
Before you can analyze data in Excel, you have to make sure that the data is cleaned up. I've helped several clients who get a data dump every month, and they have to create reports from that data. A common problem is a column that has dates and times in one cell, like the one shown below.
If you want to make pivot tables, it's much easier if you can separate the dates and times first. A simple way to do this is with the INT function -- it returns the date from a date/time combination. In the table below, there are formulas in the Date column (B) and the Time column (C).
Date: : =INT([@DateTime])
After you enter the formulas, format those columns with your preferred Date and Time formats.
Another method is to use the Text to Columns Wizard, which works well if you have enough room.
If you're bought a copy of my Data Validation Multi-Select Kit, you can make a small change to the code, so it will run faster in large workbooks. Instead of setting up the code to run when a cell is selected, change it so the popup list only appears when a cell is double-clicked.
Here are a couple of Excel articles I read recently, that you might find useful.
Sheet Protection -- Did you know that you can protect a worksheet with one string of characters, and unprotect it with a different characters? On the ExcelMate blog, Tom Urtis explains why that can happen. So, if a workbook contains information that people shouldn't see - don't share it with them! (Level - Intermediate)
Find Excel Help -- If you're stuck on an Excel problem, you can usually find help from Google. Brad Edgar shares 8 ways to search for Excel content. I often use tip #4 to look for things on my own site! (warning - persistent popup form). (Level - All)
More Excel Articles -- Find links to more articles in the latest Excel Weekly Roundup on my Contextures blog -- you'll see an animated chart with a dinosaur chasing a human.
And for a bit of humour, read the weekly collection of Excel tweets.
On Friday, a mysterious package arrived from 511 Food Service. I wasn't familiar with the company, but the parcel wasn't ticking, so I opened it up. At first, I was confused -- the parcel was filled with a colourful assortment of rubber gloves. Had I received someone else's order by mistake? Was the universe telling me that it was time to clean the house?
Fortunately, there was a note in the parcel, and after I read it, I burst out laughing. The gloves were a gift from John McLaughlin, the company's Managing Director. He reads this newsletter, and saw last week's picture of my marbled paper project (and hands). Now my grandkids and I can safely do craft projects for the next 20 years! Thanks John, for both the gloves and the laugh.
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:
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.
Last updated: June 14, 2017 11:44 PM