Excel Tips and Tricks

Sorts and Filters in Excel by Kelly Lynn

  Working with large data files in Excel can be daunting because as the size of the spreadsheet grows, it becomes harder to read and digest the data. Excel has built-in sorting and filtering functionality to help make large spreadsheets easier to use. Learning about the sorting and filtering options in Excel will help you move through your spreadsheets more easily and efficiently. Sorting - Sorting is a great way to make your data more usable. Excel's sorting function lets you arrange various columns in ascending or descending order. This is helpful when you need to sort text in alphabetical order or when you need to sort columns with numeric data. To do a very simple sort, simply place your mouse in the column you want to sort on and … Continue reading

Microsoft Excel 2007: How to Edit/Delete Macros and Save Your Macro Enabled Workbook by Kelly Lynn

  If you make a mistake when recording a simple macro, the easiest thing to do is delete the macro and start again, but it is also possible to edit the function of a macro using the Microsoft Visual Basic Editor. This tutorial will explain how simple changes can be made to macros that require little or no understanding of Visual Basic Editor. More complex editing requires an in-depth understanding and so will not be explained here. The special actions that must be performed in order to save a macro-enabled workbook correctly will also be outlined. To edit a macro: . Click the Developer Tab within the Ribbon . In the Code Group, click on the button marked Macros . Choose your desired macro from the list click Edit . … Continue reading

Microsoft Excel 2007: Useful Macro Information by Kelly Lynn

  This tutorial deals with some useful features and shortcuts that Excel has to offer when using macros within your work. By the end you will be fully proficient in creating a shortcut key, along with assigning and deleting a button for a macro. Create a macro shortcut key: . Click the Developer Tab found in the Ribbon . Click into the Code Group - Macros button . The Macro dialogue box will then be displayed and you will see a full list of all your macros . Choose the relevant one from the list and click the button marked Options . This will display the Macro Options Dialogue Box . Have a look at the Shortcut key box - if anything already appears in there, you will need to … Continue reading

Macro Security in Microsoft Excel 2007 by Kelly Lynn

  It is important to remember that macro-enabled workbooks can hide nasty viruses that can seriously damage your computer files. When receiving a workbook that is macro-enabled off another person (either via email or the Internet) your first consideration should always be security. The security settings in Excel will allow you to open and edit the workbook, but the macros will NOT work.[/font] There are 4 levels of security that can be set to prevent any damage from infected files: Disable all macros without notification: All macros in the workbook will be disabled upon receipt and you will not be made aware that they were there at all. Disable all macros with notification: This option shows you all the available macros and you have the choice to enable them or … Continue reading

Linking Data in Microsoft Excel 2007 by Kelly Lynn

  In this tutorial, linking your data in Excel will be explored. You will learn how to link information contained in a single workbook or across multiple worksheets/workbooks. The SUM function and using the Shift key across more than one workbook will also be explained. The tutorial concludes with instructions on how to copy and paste information in Excel, while still retaining the link to the original values. Linking data The most important feature of spreadsheets is the ability to reference the contents of cells and their ranges, to be able to create charts and perform the calculations you require. When you are working with formulas in Excel, you have the ability to use cell references that refer to cells in: . The same worksheet . The same workbook but … Continue reading

Microsoft Excel 2007: Formula Auditing and Error Tracing by Kelly Lynn

  Excel has a group of functions which allow you to audit your formulas and trace where you went wrong if there are errors in your work. They also allow you to view which cells a particular formula is calculated from, known as 'precedents' and which cells depend on what is placed into another cell, known as 'dependents'. Collectively, these tools are known as the Formula Auditing Group and you must display this manually:   . In the Ribbon you will see a tab marked Formulas . Click on this to display the Formula Auditing Group How to Trace Precedent Cells Tracing a precedent cell simply refers to showing how the current value of a cell came to be, i.e. - which other cells were involved in calculating that value. … Continue reading

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