Home » Posts Tagged "excel"

Business Administration Certification – Get Started!

With new small businesses coming on to the scene daily, it can be intimidating for any entrepreneur to jump into the fray. There are many skills that you need to know in order to be successful in today’s tech-savvy marketplace, and it’s hard to know where to start. For most companies, from the design sector to the financial sector, there are several key software programs that must be mastered for maximum efficiency and workflow. Luckily, you can get all the training you need to launch your own business right from the comfort of your own couch!

read more

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.

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

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

A Simple Mail Merge Can Save You Time

[Originally published in The Coloradoan on 4/6/12] If you have a list of contacts on your computer and are ready to start mailing to a large group of them, then you can put down your pen and let your computer do all the dirty work. With two of the most popular Microsoft programs, Word and Excel, you can quickly and easily create a mail merge system for your contacts. A mail merge is the combination of a data source and a word processing document to produce mailing labels, envelopes, form letters and more. It is an extremely powerful tool that most administrative professionals use to save tons of manual labor time. The way a mail merge works is that you must begin with some kind of data source. This can come in many forms including a table from a database, a list of your Outlook contacts, a CSV file or even a simple Excel spreadsheet. Since most contact applications can export to a spreadsheet format, Excel can be used in a variety of scenarios for your business. Once you have a data source prepared properly, you can open Microsoft Word and begin the mail merge process. This begins with either a blank document or you can even use a pre-existing document. Envelopes and mailing labels come in standard sizes, and Microsoft has these already built into the program, too. By inserting placeholder fields into the mail merge document, you are allowing the program to replace the placeholders with actual values from your data source. This will repeat for every record in your data source. In a form letter example, you also have the option to customize individual letters, just in case you want to send a more personalized message to an individual client. When it is time to print the mail merge document, you simply finalize your merge and advance to print the entire batch. A mail merge is an amazing tool for simple administrative tasks, and the reach of how it might save you time is limitless. So, the next time you need to send those holiday cards to a huge list, check out a mail merge and save yourself some precious time. Stu Crair is the owner and lead trainer at The Digital Workshop Center, providing digital arts and computer training instruction in Fort Collins. Call him at (970) 980-8091 or send email to stu@...

read more