How can you use a formula to find the last item in any category, in a sorted list? Someone asked me that question last week, and I used a combination of INDEX and MATCH to find the solution.
Author: Debra Dalgleish
Excel Roundup 20140203
I’ve seen Excel used to create art, and it can also be use to make bingo cards. This week, I read about a teacher who combines those techniques – she makes art history bingo cards in Excel.
Here’s a picture from her blog post, and you can download the sample file from her site.
Contextures Posts
Here’s what I posted last week:
- Have you shared files online with the Excel Web App? I found some pros and cons while using it for project tracking.
- In addition to sorting a pivot table’s values from top to bottom, you can also sort the values or grand totals, from left to right.
- If you import data into Excel, the dates might not format or sort correctly, and you can take a couple of steps to fix them.
- Finally, for a humorous peek at what other people are saying about Excel, read this week’s collection of Excel tweets, on my Excel Theatre blog.
Other Excel Articles
Here are a few of the Excel articles that I read last week, that you might find useful:
- Even if you don’t like statistics, or it makes your head hurt, take a look at Mike Alexander’s article on identifying outliers in your Excel data. You’ll feel smarter by the end of the article!
- If you’d like feedback on a chart or other data visualization that you’re working on, check out the new forum – HelpMeViz. Even if you don’t submit your own work, you’ll benefit from reading the comments there.
- If you need to compile data from several Excel files into one, you can use the sample code that Winston Snyder shared on his Data Prose blog.
- There was traffic chaos in some parts of the southeastern USA last week, when snow and ice coated the roads. To help out, volunteers set up a spreadsheet, to organize a rescue of stranded motorists.
- How do you like to learn new computer topics? Video? Books? Dive right in? Videos put Simon to sleep, and he wonders if you feel the same.
What Did You Read?
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.
__________________________________
Excel Dates Won’t Change Format
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.
Project Tracking with Excel Web App
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.
Share the Project List by Email
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.
Share the Project List Online
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.
Work in the Desktop Version
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.
Save Your Work
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.
Web App Features
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.)
Room For Improvement
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.
- Ctrl + ; – Enter the current date
- Ctrl + ” – copy from the cell above
- End + Down Arrow – move to the bottom of the range
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.
Your Experience With the Web App
- Have you used the Web App to share a file this someone else?
- Did it work well?
- What desktop features would you like added to the Web App?
- Would you like to see Shapes in the Web App? Click here to vote in the Excel team’s Web App Shapes Survey
_____________________
Excel Roundup 20140127
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.
Contextures Posts
Here’s what I posted last week:
- Select multiple items from a drop down list, and the matching codes appear in the adjacent cell.
- Change a pivot table setting, so you can apply multiple filters per field.
- Use option buttons to select a survey response, and calculate the total score, based on a lookup table.
- Finally, for a humorous peek at what other people are saying about Excel, read this week’s collection of Excel tweets, on my Excel Theatre blog.
Other Excel Articles
Here are a few of the Excel articles that I read last week, that you might find useful:
- Jon Peltier shows how users can select specific data to display in an interactive chart. There’s a screen shot below.
- Is your Excel file big and slow? Jeff Weir suggests a couple of ways to fix things.
- Doug Jenkins tested a few ways to find the maximum absolute value in a range.
- Using option buttons, Oscar Cronquist shows how to highlight groups of data in a bar chart.
- John Gagnon uses calculated fields to show marketing results in a pivot table
- Andrew Wulf describes how he developed the Macintosh spreadsheet software, Trapeze, and how a single bad review brought sales to a halt.
- If you thought that Excel was only for business, Sophie explains how she found romance in a spreadsheet.
- It’s a pain to program Winzip from Excel, and Scott Lyerly shares his Class Wrapper for Winzip, to make the task easier.
- On her Data Savvy blog, Meagan Longoria learns how to fix things after renaming fields in Power Pivot. The Excel Ribbon helped, rather than the Power Pivot Ribbon.
Upcoming Courses
No matter how long you’ve been using Excel, there’s always something new to learn.
- Sign up for one or more of the free Business Analytics webinars offered in the 24 Hour PASS series, sponsored by Microsoft, Cisco and Dell. Topics inlclude: “Advanced Analytics in Excel 2013”, “Querying in DAX”, and”Predictive Analytics for Absolute Beginners”. The online sessions run on Feb 5th and 6th, and you can see the schedule here: 24 Hour PASS Schedule
- Mynda Treacy’s Excel Dashboard course is open for registration, and I highly recommend this online course, which has excellent content, and great student support from Mynda. Click the banner below, for more information.
What Did You Read?
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.
__________________________________
Calculate Survey Scores with Excel Option Buttons
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”
Multiple Selection Drop Down With Codes
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
Excel Roundup 20140120
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:
- Compounded Madoff-ly: After years of dubiously high interest, it will come out that I’m just typing random numbers into the spreadsheet.
- Compounded sporadically: Your balance grows whenever I remember to update the spreadsheet.
Each option is illustrated with a bad drawing, like this one.
Contextures Posts
Here’s what I posted last week:
- Use a macro to link worksheet check boxes to specific cells, so you can track the responses.
- Then, assign a macro to each check box, so it enters the current date when checked.
- Change a pivot table layout to see which customers have not purchased your products.
- Finally, for a humorous peek at what other people are saying about Excel, read this week’s collection of Excel tweets, on my Excel Theatre blog.
Other Excel Articles
Here are a few of the Excel articles that I read last week, that you might find useful:
- On the Digital Forensics Stream blog, Jason Hale shows how to find the last saved location of an Excel 2013 file.
- At the Daily Dose of Excel blog, Jeff Weir makes a case for investing in corporate Excel training, and you can add your opinion in the comments.
- Alex Roe, in the Italy Chronicles blog, wonders if Excel can save Italy, as one of their political leaders suggested.
- If you’re looking for an art project to keep you busy this winter, see what they created at Maison H & H – a wall hanging based on an Excel worksheet.
- Instead of creating a long concatenation formula, Chandoo shares a quick trick for combining the values in multiple cells.
- Dennis Wallentin recommends that every serious Excel developer should read the VBA Enumeration Database article that’s in the CodeProject newsletter.
- Winston Snyder couldn’t find a pivot table style that he liked, so he wrote a macro to format specific ranges in the table.
Upcoming Courses
No matter how long you’ve been using Excel, there’s always something new to learn.
- Sign up for one or more of the free Business Analytics webinars offered in the 24 Hour PASS series, sponsored by Microsoft, Cisco and Dell. Topics inlclude: “Advanced Analytics in Excel 2013”, “Querying in DAX”, and”Predictive Analytics for Absolute Beginners”. The online sessions run on Feb 5th and 6th, and you can see the schedule here: 24 Hour PASS Schedule
- Mynda Treacy’s Excel Dashboard course is open for registraion, and I highly recommend this online course, which has excellent content, and great student support from Mynda. Click the banner below, for more information.
What Did You Read?
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.
__________________________________
Click a Check Box to Run a Macro
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.
Link Check Boxes to Cells With 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.