If you’ve upgraded to Windows 8.1 you’ve no doubt noticed these really $*&^%@#! irritating stickies that show up if you inadvertently hit a corner with the mouse and they won’t go away. Naturally, in Microsoft’s infinite wisdom (same as with the whole Windows 8 BS), there’s no native option to turn them off, so here’s a Registry edit that will do it:
Disable Windows 8.1 “Help” Stickies
And as you’ve probably noticed, the “new” Start Button is next to useless, but here’s some freeware that will create an actual working Start Button:
It looks like Steven Sinofsky’s replacement didn’t listen to consumers anymore than he did. The fact that under the hood Windows 8 is better than Windows 7 doesn’t mean squat if it’s still a freaking pain in the ass interface. As far as I’m concerned “Metro” has no place in a business environment.
I’ve been fortunate to have had some fantastic opportunities to travel around the country facilitating Excel training, and along the way found that a lot of people who use Excel in a business environment receive little, if any support for how to actually use Excel. The result is often outright frustration in not knowing the best ways to perform tasks with Excel. If you’re one of the lucky ones who is considered the “Office Excel Guru”, then you know this all to well. In fact, in my life in a Fortune 500 corporate environment, I was just that person, and found a lot of my time spent helping others be more effective in Excel. Another thing that I saw all to often was how poorly the “professional training material” that is provided to many organizations is sorely lacking in terms of business relevance. As a result, I decided to write a book on Excel focused specifically at how to use Excel in a business environment. My overall goal with the book is to show you how to make Excel work for you vs. you working for Excel.
Excel 2010 - Business Basics & Beyond
The true inspiration for the book was my old boss, who for years I tried to help with Excel, but more often than not I simply ended up doing it for him because it was much less frustrating. When he reviewed the book he asked me outright why I hadn’t done it earlier.
Here’s a brief description of what you can expect:
Microsoft Excel is one of the most powerful tools a business owner, manager, or new employee has at their disposal, and this guide teaches how to harness business data and put it to use. Using real-world examples of a small business in operation, the book covers topics such as preparing financial statements, how to best display data for maximum impact with formatting tools, data tables, charts and pivot tables, using customer information to create customized letters with mail merge, importing data from programs such as QuickBooks, calculating the costs of doing business with financial formulas, and much more. Helpful screenshots are spread throughout the text, and the book explains how to find ready-made templates online for free.
The book begins with how to customize Excel so it suits your style/needs (especially with regards to the “Ribbon” user interface, new with Excel 2007), then follows a natural progression of setting up a workbook project as you would in a business environment. This involves discussing intelligent spreadsheet design, entering/editing data and formulas, followed by the analytical tools you have at your disposal, finishing with how to import/export data from/to other sources. There are also downloadable sample workbooks for each chapter, so you can follow along with the content in Excel on your own. As for the content itself, I put a lot of effort into making note of shortcuts, as well as tips and tricks that you can use in your daily Excel work to help you become more efficient. After all, if you can save time, then you become more productive, and hopefully happy with what you’re doing.
Here’s a breakdown of the chapters:
Lesson 1 – Introduction to Excel
Lesson 2 – Basic File Operations & Setting up Excel the way you want it
Lesson 3 – The Ribbon In Depth – The Home Tab
Lesson 4 – The Ribbon In Depth – The Rest of the Ribbon.
Lesson 5 – Entering and Manipulating Data & Basic Rules of Spreadsheet Design
Lesson 6 – Using Functions (you know, formulas and stuff like that)
Lesson 7 – Formatting & Printing
Lesson 8 – Working with Graphics
Lesson 9 – Charts (this is one of the most time consuming elements for most Excel users, and this chapter shows you how to save tons of time!)
Lesson 10 – Excel Tables (an immensely under-utilized Excel feature – Once you start using them you won’t go back!)
Lesson 11 – Pivot Tables (one of the most powerful analytical tools you have at your fingertips)
Lesson 12 – Importing Data from other sources & Mail Merge
Excel 2010 – Business Basics & Beyond is by no means a be-all-end-all book and couldn’t remotely be compared to one of the Excel “Bible” publications, but if you are frustrated with Excel and want to become more productive with it, then this is a very affordable and easy to read start.
You can find it at Amazon (for Kindle), Amazon Paperback, and at the Mr. Excel Bookstore.
Many people (especially in Finance) use Excel and Access almost interchangeably, feeding data between both applications. But if you’ve never done it, then how do you? There are several ways to import data from Access to Excel, and you don’t even need to have Access open. You can import Tables, as well as most queries (Crosstab queries aren’t supported), so it’s up to you to determine which you want to import. In general, since you use Access to store large amounts of data in tables, then use its fantastic querying ability to pare that data down into manageable chunks, you’ll find yourself importing query results most of the time.
In this article we’ll discuss the easiest way to import Access data into Excel whether from Tables or Queries. To get started go to Excel’s Data menu–>Get External Data–>From Access (this was a new option with Excel 2007 and the Ribbon Interface), although if you’re familiar with it, the old MSQuery option is still there, which we’ll discuss in another article.
Get External Data
From there, select the database that you want to use as your import source. Excel will then display a list of the available Tables and Queries that can be imported:
Select Table/Query Dialog
In this case we’ll import the Customers table. Once you’ve selected the Table/Query that you want to import, Excel will ask you how you want to import it. For this example we’re just going to import the Access data as a table, although you can see that Excel gives you several options for importing the data. You should note here that while you can’t currently import Crosstab queries from Access, you can import the data that supports the Crosstab as a Pivot Table report (after all, a Pivot Table is essentially Excel’s version of an Access Crosstab query). Another option there is to create a Select query using your Crosstab query as the data source, then import the resulting Select query.
Sidenote: If you’re using Excel 2013 you’ll see a check box at the bottom to “Add this data to the Data Model”. This is an exciting new feature that the Excel team developed that allows you to create relationships between tables (a lot like you’d do in Access when building queries). If you’re interested, then make sure to check out this article from Diego Oppenheimer, Program Manager for the Excel development team at Microsoft: http://blogs.office.com/b/microsoft-excel/archive/2012/08/23/introduction-to-the-data-model-and-relationships.aspx.
Import Data Dialog
Properties – While you can certainly get detailed with Import Properties, for this article I just wanted to point out a feature that’s handy if you have a data set that refreshes at regular intervals: you can tell Excel to automatically refresh your Access data whenever you want.
Skipping past the Data Model and Properties, if you hit OK your table data will be immediately returned to Excel:
Imported Data in Table Format
If you’re at all familiar with Excel’s Tables, then you’ll recognize this format, which you can modify in the Table Tools Design tab, which will become active when you select any range in the table.
If you haven’t taken advantage of the Refresh options in the Properties dialog, you can refresh on demand from the Table Tools Design tab or the Data tab.
Table Tools Refresh Option
Data Tab Refresh Options
Note: The database examples used in this article were from Microsoft’s Northwind database for Access 2010, which you can find under File–>New–>Samples.
Note: this article was written specifically for Excel 2010 and its Ribbon interface, however the concepts reviewed will apply for the most part to earlier versions.
In our last article we discussed how Excel is a fantastic tool for creating forms, and reviewed the native Data Form, which is built into Excel (but you need to work to find it in Excel 2007+). It’s a simple tool for entering transactional data, like check register or vehicle service information, but isn’t a great tool for one-off purpose-built forms that generally only need to capture your information one time, like an application form, employee expense report or time-off request. The next form type we’ll discuss is the worksheet form, in which you model a worksheet to mimic what you would see with a printed form. In fact, many companies make forms like this in Excel so they can replace pre-printed forms, and it saves quite a bit of expense, both from not having to send something to a commercial printer, as well as being able to capture data electronically (although tragically, most companies that employ digital forms – in Excel or specific form building software applications – never do anything more with that digital information). Another key point about this article is that we’ll be talking about how to design an intelligent user-entry form for one-time use, not being able to actually capture the data in a transactional sense, like keeping a record of invoices you generate. That is easy enough to do, but it requires VBA (Visual Basic for Applications) code, which is outside of the scope of this article. However, there will be a link to a Company Invoice template form that can capture transactional data that you’re free to modify for your own use.
As mentioned in the last article, the key to intelligent form design is making it as easy as possible for your users to give you the information that you want. If you make it hard for them to give it to you, they will make it hard for you to get it out. It’s really as simple as that. This isn’t necessarily by any conscious decision on their part, it’s just a fact that if you make it difficult for someone to give you information, they generally won’t be inclined to spend a whole lot of time error checking their entries. We’ve all had negative experiences filling out one form or another, so that leads to two primary principles in design: first, make it easy and intuitive for people to enter information (data), and second, structure your form in such a way that it flows from point to point in a natural progression. Here’s a simple example for entering phone numbers: most people will enter “(212) 555-1212”, and waste time with the parentheses and hyphen, when Excel has a Telephone number format that will accept “2125551212” and automatically format it as “(212) 555-1212”. Do you have to educate your users to a certain extent, sure, but they’ll thank you once you do, and that can cascade down to how they interact with other intelligently designed forms & applications in the future. In fact, intelligent design extends far beyond just filling out forms, but how people interact with applications in the first place.
With that in mind we’ll be covering various elements native in Excel to be able to facilitate efficient design and user input:
- Worksheet Protection – Using some of Excel’s internal functionality to direct data entry, and prevent them from overwriting important formulas or other information.
- Data Validation (to limit or direct user entry) – For instance in a Time-Off request you wouldn’t want to let a user enter a date before today. Or using lists to limit what a user can select, like all of the US states, instead of giving them the opportunity to misspell Mississippi (or in one case I saw not too long ago: “Missasagua”, and that’s probably misspelled).
- Conditional Formatting – You can use this to indicate to a user that a cell needs to have an entry.
- Cell Formatting – Just like the telephone number formatting example.
- Worksheet Formulas (to prefill entries based on a Data Validation selection) – Let’s say you have a customer list in a worksheet, you can use VLOOKUP to pre-fill the customer address information as soon as the user selects Customer Name from a Data Validation list.
- Worksheet Controls – Check Boxes, Option Buttons, and other web-type features that make it easier for your users to fill out a form faster. An example would be a “Tax Required” button on an Invoice form that could then be used to calculate the appropriate tax.
None of the methods we’ll be discussing are infallible, the data/input that you receive is only as good as the users giving it, and there will always be mistakes. But if you can limit that on the front end, then you greatly simplify both your life and theirs, and you can all subsequently spend more time on more important things.
Worksheet Protection – What is that anyway? Well, by default all cells in an Excel worksheet (over 1 billion now) are protected. Which means that if you protect a worksheet, then user entry into any cell is restricted. But you can use that to your advantage, so let’s say that you want to allow a user to enter information into certain cells, but not alter others, then you simply unlock those cells and protect the worksheet. Once you do that users can only enter information into those unprotected cells.
NOTE: you can select multiple, non-contiguous cells with CTRL+Left-Click, then CTRL+1 launch the Format Cells dialog. From there goto the Protection tab and uncheck the “Locked” check box. When you protect the worksheet afterwards, then just those cells will be open for entry. The Hidden option is for formulas; when you select that then users will only see the results of formulas, as opposed to the actual formulas themselves. This can be very handy if you have novice users who might get confused when they see formulas (or if you have a very complex formula that you don’t want to share).
An added bonus to Worksheet Protection is that it introduces a natural Tab order (meaning that your users can use the Tab key to move from each unprotected cell to the next). A key note here with regards to design is that the natural Tab order goes from Left-to-Right, then back down to the next row and does not deviate, so you need to keep that in mind with your “intelligent” design. By way of example, think of any Internet form you’ve filled out where you tabbed to different fields in the wrong order, requiring you to redirect to go back to the right place. What’s that do? Wastes time right? While it might not seem consequential, think of a company like Verizon with 200,000 employees. If they have a poorly designed form that even only 20% of the staff use, and they spend an extra x minutes per week/month working with an inefficiently designed process then you can only imagine the cascading effect that can have on their internal efficiency, and subsequently why our rates go up each year. Another thing that taking advantage of the natural Tab order does is keep your user’s fingers on the keyboard instead of transitioning to the mouse to move from cell to cell. Anytime you can eliminate wasted motion like that is a good thing and again, it might seem inconsequential, but it adds up over time. And time goes two ways, you waste it or save it, but you can’t get it back.
Once you’ve selected the cells you want users to be able to enter data into you can goto the Review Tab and select Protect Sheet. Or in the vein of efficiency, use the keyboard shortcut ALT+T+P+P, both of which will display the Protection dialog:
Worksheet Protection Options
Beginning with Excel 2003 Microsoft added significantly more protection options, some of which you might find helpful beyond just locking/unlocking cells. Deciding which options to allow is entirely up to you, and it may well very change from form to form, so it’s well worth experimenting with the options to see how they all work.
Standard worksheet protection with unlocked cells should suffice for most forms, but it does take some thought when you plan the form to ensure that you can follow the Tab Order directionality. There are other ways to allow tabbed behavior using Defined Name Ranges and/or VBA (Visual Basic for Applications) code, but that’s another discussion. You’ll find more details on worksheet protection in this article: Automating Data Entry and Protecting your Data.
Data Validation – This is one of the most powerful tools you have when it comes to building powerful, user-friendly and intelligently designed forms. Data Validation allows you to direct a user to only input specific information, like select from a list of States, input a certain range of numbers, or dates. For example, if you have an Employee Time-Off request, you wouldn’t want someone to be able to enter a day before today. Or you might have an Annual Employee Performance Appraisal and want to limit a recommended annual merit increase to a certain percentage. For more information on how to set up Data Validation, see our previous article here: Automating Data Entry with Data Validation Lists.
The example workbook for this article is a very simple customer information form. Here is an example of using Data Validation to allow a user to select from a list of Titles:
Data Validation List
Notice how the cells requiring are shaded orange, but the Data Validation cell where “Mr.” has been selected is white? That brings us to our next topic, which is Conditional Formatting.
Note: Excel internally recognizes Data Validation input on a worksheet, and it’s captured with the VBA Worksheet_Change event, which can be a very powerful tool for directing workflow based on a user’s selection.
Conditional Formatting - Once you’ve set up your form, and for this article we’re using a very simple example, you can use Conditional Formatting to focus the user’s attention on where they need to enter data. This won’t stop someone from submitting your form with incomplete information (you can do that with VBA), but it serves as a visual clue that they need to fill in certain fields. To employ Conditional Formatting, simply select the cells that you want shaded (HINT: this can be combined with the steps to unlock cells, as you have already selected the cells, so why repeat it?), goto the Home tab and select Conditional Formatting, or use the keyboard shortcut ALT+O+D. In this case we applied a very simple condition that simply checks to see if each cell has a value of nothing (“”) or not. If the condition is true, then Excel applies the format, otherwise it won’t. There are a lot of features available in Conditional Formatting, far too many to review here, so please spend some time getting used to it. Beginning in Excel 2007 Microsoft greatly enhanced your capabilities with this tool, so it’s well worth getting to know. For instance, you were previously limited to 3 conditions, now you have 64. Although please think conservatively, as actually having that many conditions could cause just as much confusion as not.
Note on Quotes: when you initially enter =”” in the Conditional Formatting Rule criteria and confirm it with OK/Apply you probably won’t see anything happen, which might lead you to believe that you did something wrong. You didn’t, but Excel will initially change the formula to =””””””, so first hit Apply, then go in and Edit the rule and remove the additional quotes that Excel added. Unfortunately, this is by design as Excel has a hard time with quotes (and apostrophes), as they have internal functions as well as what you’re trying to do with them.
Cell Formatting – The next element of form design is using custom cell formats to your advantage. It’s all too common to see users fill out forms using literal formats, like entering “(212) 555-1212”, which is unnecessary, as you can tell Excel to do it for you. For forms especially, there’s even a series of “Special” options that have been pre-built for you. In this case we’ll select Phone Number. Now all your users need to do is enter “2125551212” and Excel will automatically format it for you. Cell formatting also preserves the integrity of a value, whereas user entered punctuation can render it nearly useless as Excel will then read it as text. It is possible to get to the value with the use of some formulas, but why go to that extent if you can avoid it up front? The key is to use Excel’s tools so that it works for you, as opposed to you working for Excel (which happens a lot). Fortunately, Excel’s pretty smart, so it knows a lot of things, like “$123.45” is a number even though the $ has been added by hand, but you should never count on it, just like you shouldn’t anticipate that users won’t make mistakes (especially since we all do).
Format Cells - Phone Number
Worksheet Formulas – You can use formulas to your advantage in Excel forms, and it is one of its most powerful features. While we’re not going to get into a full-blown formula discussion here, as there have been entire books devoted to the subject, we’ll cover a few concepts to give you an idea of what can be done, which is primarily having Excel perform calculations so your users don’t have to do it themselves (can you say error management?). Let’s go back to the Employee Performance Appraisal example. Imagine that you have a list of all employees on another worksheet (Employee ID, Name, Hire Date, Hourly Salary, etc.), you can use formulas to populate all of that information simply by selecting the Employee ID from a Data Validation list. Here’s an article on IF statements & LOOKUPS, which allow you to do that. You can also use a method called Concatenation to combine information from certain cells and create unique text strings. For instance, let’s say that your employee e-mail naming convention is “LastName.FirstName@Company.com”, and you have First Name in cell A1 and Last Name in cell B1. You can use:
Which would create “Jones.Bob@XYZco.com”. The key here is the Ampersand (&) operator, which lets you link both cell references and values. Since that formula creates a text string you can coerce it to be a hyperlink by using the following:
This brings up an interesting point that if you try to do this with numeric references you’ll lose any number formatting you might have applied as Excel now sees that data as text, but you can use the TEXT function to coerce it back within your new text string. The following example lets you concatenate text and today’s date all within one cell:
=”Report Printed on: ”&TEXT(TODAY(),”mm/dd/yyyy”)
Which would read: “Report Printed on: 07/09/2012”. Otherwise you’d see: “Report Printed on: 41099” (41099 is Excel’s way of storing the date value). Pretty slick, huh? I liken this to Excel knows that I’m on Windows 7, it knows my system’s regional preferences, but it doesn’t know my intention with the value I’m referencing, even if I’ve already formatted the a referenced cell properly, so it runs home and converts the value into its basest element, in this case the system date value.
Worksheet Controls - The final element to worksheet forms is using worksheet controls, of which there are two distinct categories: Form Controls & ActiveX Controls. These are items like Check Boxes, Option Buttons, List & ComboBox controls, etc., all of which can be used to simplify form use. For example, you could add a Check Box to a form for “Tax Required?”. Once checked it would trigger a formula that will calculate the Tax due on a purchase order. For the purpose of this article, we’re only going to discuss Form Controls, as they can be formatted and controlled within Excel itself. ActiveX controls on the other hand are VBA components and you generally need to write code against them in order to expose their full capabilities. While they are more flexible and robust than their Form control counterparts, they are more difficult to master.
The first thing you need to do is expose the Controls themselves, which are located on the Developer tab in the Ribbon. As this also exposes VBA elements, Microsoft chose to hide this Ribbon element by default, since unfortunately most Excel users will never use it. In Excel 2010 goto FileàOptionsàCustomize Ribbon and check the “Developer” check box in the right-hand pane. In Excel 2007 this is a check box under the General options.
Show the Developer Tab on the Ribbon
Once you confirm this you’ll now see the Developer tab appear on the right side of the Ribbon. From there you’ll see a Ribbon group called Controls, and clicking on the Insert button will display the controls that you have available:
Like Conditional Formatting and Formulas, there are so many options here, that we’re just going to briefly touch on the topic with a general example. But you should experiment with as many of the controls as you can. Once you do you’ll probably find yourself using them all the time, even for your own work. One of the most common controls to use is the Button. Buttons are great because you can assign macros to them to perform certain tasks, like print a form once it’s been filled out. What’s really cool is that you can assign a macro to almost any object. Form Controls are objects, but so are any of the shapes you’ll find from the InsertàShapes Ribbon group. I prefer to use shapes to assign macros to since they’re much more visually appealing, and while it may seem inconsequential, people will actually fill out a well-designed and appealing form much faster and with less errors than one that‘s poorly designed.. Which would you rather see:
Form Button vs. a nice Shape Button
Building a good form not only takes functionality into consideration, but needs to appeal to the end user as well. Just think about how much easier your tax forms would be to fill out if they were thoughtfully designed.
Note: in the example workbook both buttons have a macro assigned to them to display the worksheet form in Print Preview mode, so you’ll need to enable Macros in order for them to work. You’ll find an Introduction to Macros article here: Automating Tasks with the Macro Recorder – First Steps.
For our working example we’ll add a Check Box to our form to indicate whether our contact wants to receive opt-in e-mails. From the Form Control dialog simply select the Check Box control (the Form control, not ActiveX), then draw it on your worksheet with the mouse.
Check Box Form Control
From here you can right-click the Check Box to gain access to its internal controls and formatting. Obviously you want to edit the text to suit your needs, but you can also do things like linking the control to a particular cell. When checked the control has a value of “TRUE”, and when unchecked its value is “FALSE”, so you can perform calculations against that. Most of the time you’ll put the linked cell in either a location away from the form itself, or format its text color to match the form background so it doesn’t display. If a user doesn’t need to see the TRUE/FALSE value, then don’t show it to them. There’s no need to confuse people by displaying something that they don’t need to see.
Format your Form Control
In this case the Check Box is linked to cell B11, which will toggle/display TRUE/FALSE depending on whether it’s checked or not. As you see in the Value options, you can decide whether the Check Box’s initial state should be Checked or Unchecked. A quick note on formatting is that you can’t change the font size with Form controls. That’s controlled strictly by the active worksheet’s Zoom setting. On the other hand, you can manipulate font attributes with ActiveX controls.
Using a Formula based on a Check Box's value
In this example the formula in cell A15 simply reads:
=”This customer “&IF(B11=TRUE,”does”,”does not”)&” want to receive e-mail communication.”
This is another example of using concatenation to create custom messages for your users, or perform calculations that they might otherwise need to do manually. Granted, these are simple examples, but they should give you an idea of what you can do. If anything it will help you realize how you can set things up in a time-saving and well thought out fashion to make it easier to collect information from people. The other thing it does is greatly reduce errors, which directly equates to bottom line savings.
Review – in this article we discussed the various elements that you can employ to streamline data collection processes without having to purchase an expensive purpose-built form creation software application simply by using tools readily available to you in Excel. You’ll find loads of great example forms on the internet that are free for you to use, modify and distribute as you see fit. Most notably is the Microsoft Template Gallery, which you’ll find here: http://office.microsoft.com/en-us/templates/. The key points to remember about building effective forms in Excel are relatively simple:
- If you make it hard for people to give you data, then they’ll make it hard for you to get it, so be creative, and design forms with the end user in mind. If you focus on HOW the user interacts with your form, then the data flow will be easy. If you focus on just getting the information with little or no regard to their interaction/process then you’re all in for a miserable experience.
- A well-built and intelligently designed form can greatly reduce errors, and allow your users to give you information much faster than if they had to write it out by hand, or perform manual calculations on their own.
- Use the tools that you have available to you, Worksheet Protection, Data Validation, Conditional and Custom Cell Formatting, and Worksheet Formulas and Controls.
Chris “Smitty” Smith is Director of Training and a Developer for the boutique consulting firm www.excelandaccess.com