Excel Tips and Tricks

Introducing Macros in Excel 2007 by Kelly Lynn

  When working with Microsoft Excel, you may find yourself repeating the same calculations or typing the same text in multiple cells. A 'macro' simply refers to a very useful and labor saving function within Excel that will store the action (or text) and make it available to you whenever you need it to use it. Running your chosen macro will then cause Excel to complete the process for you automatically. Follow the steps below and you may be able to save yourself a lot of time and effort! Step 1 - Display your Developer Tab Macro tools are available through your Developer Tab and you have to set this up to be displayed : . Click the Office button, and select Excel Options button . The Excel Options dialogue … Continue reading

How to Transpose Columns Into Rows in Excel 2013 by Kelly Lynn

  Excel 2013 is used to analyze large sets of data that are traditionally organized into tables; this allows for efficient application of statistics, the creation of complex analytics and the transformation of the data into graphs; usually tables are organized with the headings in the columns and the data below the headings in the rows. Often after building a complex data table it becomes evident that it would be more efficient to place headings in the left most column and have the data follow in the subsequent columns. Microsoft Excel 2013 provides an easy way to transpose the data using the simple functionality of the copy and paste feature of the worksheet. Step 1 – Select the data that you want to transpose. The example below shows a simple … Continue reading

Getting Started With Excel Functions by Kelly Lynn

  If you’re new to spreadsheets, you may think that Excel is just a set of rows and columns for storing data. Excel is a great place to keep data, but you can also use it to manipulate and analyze your information. One of the keys to learning these more advanced tasks is understanding how to create functions, or formulas, in Excel. There are built-in functions that allow you to do a variety of tasks, like adding up a set of numbers, looking for a specific value in a field, or parsing data into pieces. Let’s take a look at the steps you’ll need to take to add a simple function to a spreadsheet. For our example, we’ll use the sales data shown below: To find the total sales number, … Continue reading

Saving an Excel Document as PDF by Kelly Lynn

  Portable document format – commonly known as PDF – is a way to preserve a document’s formatting. Whenever someone prints or views a document that’s been saved as PDF, it remains formatted exactly the way you intended. Not only does this make PDF ideal for anything that you plan on reproducing (for distribution, marketing or even commercial printing) but it also creates documents that are pretty much universally accepted. PDF is ideal for newsletters, legal documents, and anything you intend to be read and printed, but not altered. Most Microsoft Office applications allow you to save your creations as PDF files, with minimum fuss. It is important to keep in mind, however, that in order to view any PDF files, you need to have an updated PDF reader installed … Continue reading

Cheat Sheet: Must-Know Excel Tips and Tricks by Kelly Lynn

  While Microsoft Excel may not exactly have a reputation as the sexiest computer application in the PC world, it is a useful one. And since chances are good you’ll need to use it over the course of your scholarly, business or personal life, having a few tips and tricks up your sleeve is a good plan. Jump to a Cell To the left of the formula bar, you’ll find a cell listing that changes to show you exactly which row and cell you’re working in. By clicking on this area, you can type in a specific column number and row name to jump directly to that cell. Fix Hinky Numbers Occasionally, Excel will erroneously store certain numbers (especially 0) as text. This can really become a hassle if you’re … Continue reading

How to Create a Mail Merge in Office 2007 by Kelly Lynn

  Are you ready to save a ton of time and maybe even impress your boss? The following tutorial will break down how to import data from an Excel 2007 spreadsheet into your Word 2007 document. The example we’ll use is to create a mailing to thank some clients for attending a seminar. First, you need some data. Here’s a spreadsheet of John and Jane with their addresses: Next, you need your document. Here’s a shot of that: Now, let’s put the two together. Once you have your Excel spreadsheet created, go ahead and close it. Then, open your Word document. Click on the top tab, named “Mailings.” Now, click on the “Start Mail Merge” drop-down on the “Mailings” ribbon. For this tutorial, we’ll go with “Letters,” so click on … Continue reading

Excel Tips: Leveraging the AutoFilter and SubTotal Functions by Kelly Lynn

  The following refers to MS Office Excel 2007. Although the information contained herein will be applicable to any version of Excel, users of another version may have to deal with minor differences in layout and display. Consider the nicely tabularized Excel database shown in Figure 1 below. Beginning in November 2012 and extending indefinitely thereafter, this database attempts to track an individual’s weekly income and expense items. It is not precisely a budget, but could be used as such. Figure 1: Weekly Income/Expense Items This is very simple data, organized to indicate regular, recurring weekly income and expense items. Very often an expense or income occurs that is not regular/recurring, and these are lumped under a weekly Miscellaneous category. If an explanation is needed, it is entered into column … Continue reading

Using Sorts and Filters in Excel by Kelly Lynn

  Manipulating data is much easier in Excel if you understand how to use the sorting and filtering features. Sorting your data makes it easier to read and understand, and filters provide you with a quick way to find specific data values in large spreadsheets. Let’s take a closer look at how to use Excel sorts and filters using some sporting goods sales data: Basic sorts – To sort data alphabetically, or in reverse alphabetical order, place your cursor in one of the cells in the column you want to sort by. Click the appropriate sort button under the “Sort & Filter” button in the upper right corner. Your data will appear in the order you specified. Sorting by multiple columns – Sometimes sorting by more than one column makes … Continue reading

Embedding an Excel Worksheet in an Email by Kelly Lynn

  There are plenty of reasons for installing Microsoft Outlook or Outlook Express. But if you spend a lot of time creating and attempting to share Excel spreadsheets, one of the most beneficial reasons might just be the ability to embed a worksheet right into an email and send it directly from Excel. Distributing a worksheet via email allows you to not only quickly share your work, but also use HTML formatting so almost anyone can view it. It also cuts down on hassle, since there’s no need for opening or downloading attachments. Start by preparing your worksheet. Open or create the worksheet you want to email. Now is when you want to make any customizations to your sheet, as well. Adding cell borders, for example, can improve readability for … Continue reading

Finishing Touches for Your Excel Spreadsheets by Kelly Lynn

  Some Excel spreadsheets end up looking polished and professional, while others look sloppy and unfinished. If you’re having trouble making your spreadsheets look their best, read on for some easy tips that will provide finishing touches for your data. Clean up the column names – Make sure your column names describe the data in the field and are capitalized appropriately. Columns names like “first_name” or “cogs” are not as easy to read as “First Name” and “Cost of Goods Sold”. Bold the headers – Column headers will be easier to read if they’re bold. This helps them catch the user’s eye and stand out from the data below. Freeze the top row – Have you ever noticed that when you scroll through a spreadsheet, sometimes the column names disappear … Continue reading