Excel tips and tutorials
Have you ever imported data into Excel, from your credit card statement, or somewhere else, and found that Excel dates won’t change format? And, if you try to sort that column of dates, things end up in the wrong order.
That happened to me this week, and here’s how I fixed the problem, using a built-in Excel tool.
When I’m working with a client on an Excel or Access project, it’s easy to lose track of everything that has to be done, as the emails fly back and forth. There are initial requirements, then things get added or changed, as we go along.
To stay on top of things, I usually make a list in Excel, showing all the steps, and marking them off when completed. Notes can be added too, in columns to the right.Later, if new items are mentioned in an email, I copy and paste those details into the master list.
Most of the time, I keep this file on my computer, and my clients don’t worry about the steps – they just want the completed work. Sometimes I’ll send an update, so they can see what’s done, and what’s still outstanding.
Sometimes, during revisions, the list goes back and forth between a client and me, and that can create confusion. Who has the latest version of the To Do list? The tracking list should help us save time, not add extra time to the project, in sorting out who added what to the list.
If we were at the same location, it might be possible to both use the same Excel file, in a shared drive. I wouldn’t make it a shared workbook though! However, my clients are usually far away from my location, so sharing the file is not an option.
A recent project was getting complicated, so I decided to try the Excel Web App, to see if it would work well for sharing the project tracking information. I had already created the file in the desktop version of Excel, so I uploaded it to my Skydrive account, and sent my client a link to the file, with editing permission. We’re the only two people who have access to the file.
Once you have the link, you don’t need to log in – just go to the web page where the file is. My client doesn’t need a SkyDrive account, and I’ve saved a bookmark in Firefox, so I can go to that page quickly. It’s easier than opening a desktop Excel file!
Once you’re on the page, you can click the Edit in Web Browser button, if you want to make any changes. Or, instead of editing, you can just look through the file, to see if anyone else has made changes, that you have to address.
If someone else is using the file at the same time, you can see which cell they’re currently editing. That will help you avoid conflicts.
There is also an option to edit the file in the desktop version of Excel, but I haven’t used that. I have Excel 2003, 2010 and 2013 installed, and when I click the button, it opens Excel 2003 – even if Excel 2013 is already open.
This option doesn’t work if someone else is also editing the file – you’ll see this warning message in that case.
When you’re editing, there’s no Save button – all your changes are saved automatically. That’s a bit disconcerting, but I’ve become used to it now.
There is an option to download the file, and I do that once a day, to keep my own archive. Those backup files have come in handy a couple of times, when I accidentally overwrote cells. while trying to drag down a column. (The interface is a little clunkier than the desktop version – that’s my excuse!)
There is also an online option to view, restore or download old versions, but you need to be logged in if you want to do that.
The Web App is a limited version of the Excel desktop program, so some of your favourite features might not be available. However, it does have the Table feature, so that makes it ideal for sharing a simple list like this project tracking file.
As you can see in the screen shot above, you can insert charts too, even though other shapes aren’t allowed. (See the Shapes survey link at the end of this article.)
The Excel Web App has been working well for sharing this simple file, but I wouldn’t want to use it for anything too complicated. Many shortcuts and features are missing (or I haven’t found them), and that can slow you down!
For example, in the project tracking table, I enter the date when I’ve completed a step, and sometimes add new items at the bottom of the list. So, I’d like to get down there quickly, and often want to copy data from the previous row.
Unfortunately, some of my favourite keyboard shortcuts don’t work, so these steps take a little longer.
On the Microsoft website, there’s a list of keyboard shortcuts that you can use: Excel Web App Keyboard shortcuts
The good news is that the Copy and Paste shortcuts work, and the cells autocomplete based on p
revious entries in the column.
_____________________
We got a Mac in April 1984, when they were first released in Canada – 30 years ago! It’s still in the computer museum in our basement (that’s a fancy term for junk pile).
At an October 1983 sales conference, Fred Gibbons (Software Publishing Corp.), Bill Gates (Microsoft) and Mitch Kapor (Lotus Development) participated in the Macintosh Software Dating Game, hosted by Steve Jobs.
In his introduction, Bill Gates said, “In 1984, Microsoft expects to get half of its revenues from Macintosh software.” I wonder what the percentage is today.
Here’s what I posted last week:
Here are a few of the Excel articles that I read last week, that you might find useful:
No matter how long you’ve been using Excel, there’s always something new to learn.
If you read any other interesting Excel articles last week, that you’d like to share, please add a comment below.
Please include a brief description, and a link to the article.
__________________________________
For easier data entry, you can add Option Buttons on a worksheet. Instead of having to type an answer to a question, just click on one of the buttons, to make a choice. Here’s how to calculate survey scores with Excel Option Buttons.
Continue reading “Calculate Survey Scores with Excel Option Buttons”
There is a sample file on my website that has VBA code for selecting multiple items from a data validation drop down list. You can insert all the selected items into the active cell, or down the adjacent column, or across a row. Today’s example shows how to make a multiple selection drop down with codes
If you’ve ever been disgruntled with your bank, you might enjoy Ben Orlin’s vision of how he would run a bank, on his Math With Bad Drawings blog. Instead of annual compounding, he’ll offer other options, such as:
Each option is illustrated with a bad drawing, like this one.
Here’s what I posted last week:
Here are a few of the Excel articles that I read last week, that you might find useful:
No matter how long you’ve been using Excel, there’s always something new to learn.
If you read any other interesting Excel articles last week, that you’d like to share, please add a comment below.
Please include a brief description, and a link to the article.
__________________________________
In the last post, we set up a To Do list with check boxes. Each check box is linked to a cell that’s 2 columns to the right, and we used a macro to set that up quickly. Now we’ll add code so we can click a check box to run a macro.
You can use check boxes on a worksheet, and link the results to a cell. If the box is checked, the cell shows TRUE, and if it’s not checked, the cell shows FALSE or the cell is empty. This makes it easy for someone to give a quick answer to a question, or select an option. You can even link check boxes to cells with a macro, so something happens automatically when the box is clicked.
In The Guardian’s Data Blog column, where the tagline is “Facts are Sacred”, things took a less lofty turn last week. You can go back to the good old days in math class, where you entertained your friends by writing words on your calculator.
In the screen shot below, you can see the cheerful greeting from our Sharp Compet CS-1130 financial calculator. I’m not sure how old it is, but it’s probably from the 1980s, and still working well. Are you still using an old school calculator?
Here’s what I posted last week:
Here are a few of the Excel articles that I read last week, that you might find useful:
It was a busy week for articles about Microsoft’s Power BI tools, so I’ve given them a separate section. Also, see the 24 Hour PASS webinars in the Upcoming Courses section, below.
No matter how long you’ve been using Excel, there’s always something new to learn.
If you read any other interesting Excel articles last week, that you’d like to share, please add a comment below.
Please include a brief description, and a link to the article.
__________________________________
Here at Contextures, there aren’t very many employees to keep track of. There’s just me, and I always remember my hire date anniversary, and take myself out for a nice lunch. However, you might have more people on your employee list, and you can use Excel to highlight upcoming employee service anniversaries.
Continue reading “Highlight Upcoming Employee Service Anniversaries”
