Home » Microsoft » Archive by Category "Excel"

Top 3 Beginner Excel Mistakes to Avoid

When beginning to use Microsoft Excel, there are several key mistakes that all students should avoid in order to be successful for the long term of a project.  Depending on your industry and specific Excel needs, this list of pitfalls can certainly become a long one. I’ve narrowed down the top 3 mistakes that all beginning Excel users should avoid.   You didn’t set your workbook up for success When you start a Microsoft Excel workbook, you need to do a little bit of project planning. Ask yourself some key questions and you will be able to use the built-in features of Excel much easier.  Questions you should ask include: How much data do I expect to include in this project over the long term? If you know how much data to expect, in terms of columns and rows, it will drastically change how you may organize the workbooks to begin with. For example, if you have data based on a date, you might want to create separate worksheets for each month (12) and an annual summary worksheet for a total of 13 worksheets.  Using this method, your data will remain smaller in size, more manageable and your risk of human error will be much lower. How can I organize the data into the smallest meaningful values? For every field of your data, you should ‘normalize’ the information. Normalized means that your data is in the smallest meaningful values.  A classic example of normalization is how you are going to capture ‘Name’ data on a contact list.  If you accept both First and Last Name into one field called ‘Name’, then how will you sort by Last name in the future?  It will be very difficult.  However, if you break this into two distinct fields, First Name and Last Name, then you have the data normalized and setup for success. Is this workbook pertaining to one subject? If you’re creating workbooks on different subjects, then you should use separate worksheets or workbooks to keep the information organized. One of the worst mistakes in Excel is to try and combine a lot of unrelated data into one worksheet or workbook.  Using Excel formulas and references you can easily tie data together across worksheets, so you should use the tools given to you for better organization of the data. You are not using Excel references properly An Excel reference is simply a way to refer to a cell, worksheet, or workbook by location instead of using constant values. By using references properly, your spreadsheets stay dynamic and, most importantly, accurate.The most common mistake when using references is to not utilize ‘absolute’ references.  By default, all cell references in Excel use a type of reference called a ‘relative’ reference.  This is important to understand because, when a cell reference is relative, the cell reference will change automatically when you copy/paste.An ‘absolute’ reference means that you always point to the same cell no matter what, so copy/paste will not affect the references used.  This a key component of Excel spreadsheet building and you must understand how to utilize effectively to be successful in Excel. You are individually formatting cells When you have your spreadsheet data ready, you should then begin to format it appropriately. However, you don’t need to continually make the same formatting choices over and over.   When you format one cell or range of cells, you can copy that formatting using a tool called the ‘format painter’.  This is essentially a tool to copy/paste the formatting only.  Format painter is a huge time saver.An even more effective formatting technique is to utilize the...

read more

Microsoft Excel Cheat Sheet

Download the Microsoft Excel Cheat Sheet for FREE! The Digital Workshop Center provides free cheat sheets on many popular software applications. If you are looking for a quick Microsoft Excel reference sheet, then we’ve got just the thing for you! The Excel cheat sheet includes several useful shortcuts and common commands to help you be more efficient and confident while working in Excel. Several of these commands are included in our hands-on Excel Level 1 training class, so if you need more Excel help please contact the Digital Workshop office.  Stay tuned for more cheat sheets coming soon! Click the link to download the cheat sheet in PDF format: Microsoft Excel Cheat Sheet...

read more

Text Functions Can Help You Master Microsoft Excel

The problems in Microsoft Excel that the average person comes across tend to be similar from one person to the next. While working on a recent project with a client, it occurred to me that there are many text functions that everyone might like to know more about. To start, all Excel functions have the ability to calculate or manipulate your data. Many people think of Excel as a resource to build financial spreadsheets and calculations, but the program can do much more than that. When working with text data, common problems arise such as capitalization issues, additional spaces, removing portions of unwanted data, and combining multiple pieces of data into one to name a few. Focusing on capitalization to start, Excel has three functions to help you. The ‘Upper’ function will turn any source data into all upper-case, the ‘Lower’ function will convert all your source data to lower-case, and the ‘Proper’ function takes any text and forces the first letter to be capitalized with the remaining letters in lower-case. For any of these text functions, you can reference another cell with the source data and display the result in separate cell with the desired case. Another example of text manipulation often arises when you are given data from a third-party system. Let’s say that you have an HR system that generates a CSV file with names and phone numbers. In order to create a list of emails for your company you might want to join the first and last name to create an email address. Using the ‘Concatenate’ function, you can reference several pieces of data and combine them into one. You might use Concatenate to have the first name, an underscore, the last name, and then your company URL (e.g. @example.com), and generate an email for every employee. In this example, you can let Excel save you a ton of time letting the Excel text functions manipulate your data versus entering the email address manually. There are several other useful Excel text functions that you may find helpful in your daily work. However, it can be intimidating to try and learn them all. I would advise any beginner to Excel functions to use the ‘Insert Function’ command where you can learn about these functions in more detail. The ‘Insert Function’ command provides a definition of the function, and a clear definition of any corresponding arguments that the function needs in order to work properly. I recommend this technique to all of my students as a great way to find and understand the right function for you. Don’t be afraid to jump in and try new text or manipulation functions. As always, make a backup copy of your data first, just in case something you do cannot be undone. Most importantly, have fun with Excel and find new ways to let the program do all the heavy lifting with your data. [Originally posted in the Coloradoan on...

read more

Using Microsoft Excel to Integrate Data Systems

Integrating multiple systems is a problem for any business. In the past few months, it’s been a good problem to have for the Digital Workshop. As our volume of students has increased dramatically, we’ve been trying to figure out how to better handle our multiple systems internally. One of the most common issues that come with having multiple systems is they don’t talk nicely to each other. By “talk,” I mean data does not transfer easily between them. For example, a system for human resources data may export a text file of employee info that a payment tracking system doesn’t understand. We may be able to import data into the payment tracking system, but only if it’s set up properly. We are then forced to figure out ways to manipulate the data to prepare it for import. Microsoft Excel is the industry standard program for data manipulation. When dealing with this type of scenario, Excel has built-in tools to manipulate text, dates, numerical data and more. One of the best examples of Excel’s power is in dealing with names that are improperly formatted and need to be moved from one system to another. Let’s say one system generates names in the order of “last name, first name.” However, your other system needs the name data as two normalized fields, such as “last name” and “first name.” At this point, you need to write a formula to split the original data into two values. The only constant we know about this scenario is that the first and last name values are separated by a comma. When we can recognize a constant of any kind, it helps us shape how to build our formula. Using Excel’s “Find” function, we can find the starting position of one text string located inside of another. In this case, we use Find to determine where the comma is located. We’ll call that value X. Next, we can take the value of X and use it nested in the “Left” function, which returns a value that counts the number of X characters from the Left side of the value and returns a result. After combining Find and Left, all that will remain is the “last name” value including the comma. Subtract one from the value of X and you’ll get a field with the last name by itself, ready for import. If you give it a try, Excel can help simplify data transfer in a number of ways like this. If you have a tech question you need answered — including more about Excel — visit Facebook.com/fcdigitalworkshop and ask. I’ll answer some of your questions in upcoming columns. [Orginally published in The Coloradoan on...

read more

Five Excel© Features That Everyone Should Know

Microsoft Excel is just about as famous for being frustratingly difficult as it is for being exceedingly useful. These five tricks will make your data-crunching experience a much more relaxing one. 1. What Day Is It? Excel’s “Today” feature is no doubt a quick way to insert the day’s date into your spreadsheet. If you are not familiar with it, entering =TODAY() into a blank cell puts the day’s date into the cell. A word of warning, though – Even though this seems like it would be a useful tool to track when you input important data into your spreadsheet, the date will automatically update whenever you open the spreadsheet. If you wish to keep a log of when important data was put into your spreadsheet, Ctrl + ; will do just that. If you wish to input the date but not the time, Ctrl + Shift + ; will do the trick. (Extra tip: Inserting =NOW() will insert the day’s date and time into the cell.) 2. Protect Your Formulas! If you have important formulas you do not want others to see (such as how employees’ bonuses are figured) you can prevent Excel from showing that formula when the particular cell is highlighted: On the Excel toolbar click Edit + Go To Click the Special tab to open the Special menu Click Formulas and make sure all four of the options are checked. Click OK and Excel selects every cell that has a formula. Select Format, Cells, and choose Protection Place a check mark next to the Hidden option, and make sure that there is a check mark next to the Locked option. Click OK 3. Take Notes! On the topic of formulas, it is also possible to add hidden text to any particular formula. This is incredibly useful to help remember what each number in the formula represents. For example: you have a budget formula that reads “1000+ 250- 200*2.” If you are having trouble remembering what each number represents, simply add + N() behind the formula. Excel will translate the +N() into 0 and it will not affect your formula. Then you can add in notes (in quotation marks) to jog your memory for the next time you open the spreadsheet. For example: =100*(1+10%)+N(“10% was the projected increase from 2011’s rates″) What this formula is actually doing: =100*(110%)+0 4. Freeze Panes As if number crunching wasn’t already painful enough, it becomes exceedingly difficult as you work farther and farther down or to the right. There is nothing more frustrating than forgetting what each row or column represents, scrolling all the way back up to remember, and then rapidly scrolling back down to begin working again. Luckily, Excel has realized this frustration and included a “freeze pane” feature that will keep a column/or moving as you move in the worksheet. Simply click and select the row below or the column to the right of where you want to freeze. Then, on the Window menu, select Freeze Panes. Viola! You will never forget what you are inputting again. 5. Where Did That Column Go? Oh, There It Is. There are a never-ending amount of situations within Excel when you just may not want to include information that was pertinent to you with others. You may be displaying productivity reports to the office, but you don’t want everyone privy to who exactly it was that somehow managed negative productivity. Excel allows users to hide columns in the spreadsheet before printing and while working. To hide a column: Highlight the columns/rows you want to hide. Click Format, Column, Hide To...

read more

Microsoft Excel: Using Conditional Formatting

[Note: This article was originally published in The Coloradoan on 2/24/12] A client recently came in looking for help with Excel spreadsheet issues. As the director of a youth program, she was using an Excel spreadsheet to track participants’ enrollment information. She needed the name of an active participant to show in green. If the participant was transitioning out the name was to show in yellow, and if the participant had left the program, the name was highlighted in red. She had approximately 3,000 participants to work with at any given time, and she manually went through every entry to review the enrollment status and change the color accordingly. My first thought was “what a horrible waste of time.” Hopefully, we all know that computers save time when doing repetitive tasks, and a program such as Microsoft Excel was built to replace paper spreadsheets and at the same time make users more efficient in our daily lives. Along these lines, a feature of Excel that I love to teach people about is called “Conditional Formatting.” Conditional formatting is when you want to format cells in your spreadsheet based on one or more conditions. Based on criteria that you as the user can set, Excel can automatically highlight cells, create data bars, use icon sets, and much more. Below is an image showing what this dialog looks like in Excel: In my client’s example, I worked with her to set a conditional formatting statement that read “if the participant was in the youth program between a specific start date and a specific end date, then we will format the cells to background highlight in green. Or else, if they are transitioning out of the program, we will use a different format to background highlight in yellow.” And so on. Excel does all the dirty work of deciding when the participant falls under which condition, and formats accordingly. All of my client’s manual work is now replaced with literally a couple clicks of the mouse. The manual process of scrubbing through data certainly works for some situations and may be your only option. However, it also leaves a high risk for human error because human beings are much more prone to mistakes than a computer. So, let the computer and Excel do what they do best, handling your repetitive tasks cleanly and let Excel pick up the slack whenever possible. Stu Crair is owner of The Digital Workshop Center. Reach him at (970) 980-8091. Want to learn more about Microsoft Excel and what it can do? Register for one of our Excel classes...

read more