Excel tips | AccountingWEB

Excel tips


AccountingWEB Webinar: Creating error proof spreadsheets

In today's fast-paced business world, spreadsheet users don't always have the luxury of double-checking every element of their work, particularly in complex spreadsheets. Fortunately there are some simple practices you can adopt to help minimize the risk of errors in your spreadsheets. You can also learn how to track down errors in spreadsheets built by others. Take an hour tomorrow and get up to speed during our live Creating Error Proof Spreadsheets Webinar.

Excel Tip: Hide an Excel Worksheet in Plain Sight

By David Ringstrom, CPAAs you’re probably aware, it’s simple to hide a worksheet in Excel 2007: right-click on the worksheet tab, and then choose Hide. To unhide the worksheet, right-click on any worksheet tab, choose Unhide, and then select the sheet you wish to make visible. It’s a little more effort in earlier versions of Excel: choose Format, Sheet, and then Hide or Unhide to change the visible status of a worksheet.

Excel Tip: Add visuals to your headers and footers

Since Excel XP it has been possible to insert a picture into a custom header or footer. Our sister site, AccountingWEB.co.uk investigated some potential uses for this facility.Excel's View-Header/Footer menu option will take you to the Header/Footer section of the Page Setup screen. Clicking on the Custom Header or Custom Footer button presents you with three sections: Left, Center and Right.

Excel 2007: Use these three techniques to turbo charge filtering

By David Ringstrom, CPADid you know Excel 2007 has a hidden command that enables you to filter a list with just one mouse click? And that there are two ways to sum just the visible rows in your filtered list? Read on to discover these time saving techniques.Not sure how to filter? Read Excel Filtering 101. 

Excel 2003: Use These Three Techniques to Turbocharge Filtering

By David Ringstrom, CPADid you know that you can streamline the AutoFilter feature in Excel 2003 and earlier versions? And that there are two ways to sum only the visible rows in your filtered list? Read on to discover these time saving techniques.Not sure how to filter? Read Excel Filtering 101.

Excel Filtering 101

By David Ringstrom, CPAFiltering is a powerful, but often overlooked feature in Excel that enables you to hide unneeded data within a list. In Excel 2003 and earlier, this feature is known as AutoFilter, while Excel 2007 refers to it as simply Filter. In this article I'll provide a quick overview of how to use this helpful feature.Filtering BasicsFiltering enables you to collapse a spreadsheet down to display only relevant rows.1. Copy the data from Table 1 into a blank worksheet, starting at cell A1. 

Excel Chart Advisor Prototype Simplifies Graphs

By David RingstromExcel 2007 users have a new tool in their arsenal: the Chart Advisor from the Microsoft Office Labs — a new proving ground for potential new Office features. Although this tool is a prototype, you may find it helpful in your daily work. This add-in for Excel utilizes an advanced rules engine to analyze your data, and then rank chart suggestions based on their respective score. Remember, this tool only works with U.S. English version of Excel 2007.

ExcelZone's Five Minute Pivot Table Tips - Index

ExcelZone's series of Five Minute tips aims to develop your skills at using Excel pivot tables. If you want to try out each tip for yourself, Excel expert David Carter takes you through a worked example on a small sample database of sales invoices.In the worked examples you will analyze the invoices to show sales by product, sales by customer, gross margins, margin percentage etc. The series is therefore relevant to sales and marketing people as well as accountants.

Excel Compendium: Practical uses for Lookup and Reference functions

Excel's VLOOKUP, HLOOKUP, INDEX, and MATCH functions can make life a lot easier for accountants.

ExcelZone spreadsheet seminar tips: Think first, Excel second

An experienced group of management accountants gathered earlier this year at the Law Society in London for AccountingWEB UK ExcelZone's inaugural seminar on "smarter management reporting with Excel." The event, the first in a series, was sponsored by ExcelZone partner Rugged Logic. ExcelZone contributor Simon Hurst was on hand to pass on tips for smoother working with accounting data and spreadsheets. "Before we get on to the practical stuff, the first thing you need to do when reporting with Excel is to design a good spreadsheet," says Simon Hurst, AccountingWEB contributor.

Excel 2min Tip: Round your budgets to the nearest thousand

If you have a spreadsheet containing a lot of numbers, it will be easier to read if you round the numbers to the nearest thousand.To display figures to the nearest thousand, use the Format – Custom feature and include three things: a dot, a zero, and a comma.Example: To display the numbers on the left as the numbers on the right, follow these steps:1021.76...........1.1560...................0.6540...................0.521.....................0.01345.26...........1.3-520.78...........-0.5Type the first column of

Excel Tip: Using the AutoFill feature for copying cells

Excel's AutoFill feature is activated by dragging the little square that appears in the lower right corner of the active cell. Here are the options for copying with AutoFill:If the cell contains a formula, dragging the AutoFill box will copy the formula to all cells over which you drag. The formula will be updated to logically respond to the information in the cells to which you copy.

The Spacebar: Your Excel nemesis

The spacebar can be your enemy in Excel. While pressing the spacebar might seem like a simple method for clearing data from a cell, the results of such an action can be devastating. And spaces take up space, and that can cause other problems with your spreadsheet.Pressing the spacebar leaves not a blank but a space in a spreadsheet cell, and a space is a character, just like a letter or a number.

Want to learn about Excel pivot tables? Start here. By David Carter

Pivot tables come free with Microsoft Excel and are a must for anyone who wants to analyse their data. AccountingWEB has been spreading the word for a long time now, and we published our first self-teach tutorial on pivot tables as far back as 1999. There's now a full suite of these tutorials and they have been accessed over 100,000 times. Why tutorials?There are dozens of books out there which will explain to you how to create a pivot table.

Critical Excel vulnerability features in July's 'patch Tuesday'

Excel 2007 featured in the security updates issued by Microsoft on July 10, the traditional second Tuesday scheduled for the company's monthly bulletins.The Excel vulnerability could allow malicious code to be downloaded and run by causing a "buffer overflow" when a user opens a specially crafted Excel file.

Excel 2007 Tip #2: Formatting is now done via the right mouse menu

One really disconcerting change to Excel 2007 is the disappearance of the toolbars. Features you use all the time seem to have disappeared back to into the menus.People with prior versions of Excel usually have two toolbars permanently on display – Standard and Formatting. The Formatting toolbar contains the icons for adjusting fonts, centring, bold, italic decimals, colors and so on.In Excel 2007, formatting is no longer done via toolbars, but via the right mouse menu.To illustrate, open up a worksheet, then right click on it. The right mouse menu appears.

Excel 2007 tip #1: Make your data visible to other Excel users by saving in Excel 97-2003 format

Files produced by Excel 2007 are in the new .xlsx format. No other version of Excel can read them unless a special compatibility pack has been installed. If you plan to share your Excel 2007 files with other Excel users, you can set Excel 2007 to save your files automatically in Excel 97-2003 format. This will allow other Excel users who have not upgraded to Excel 2007 to read your files.How to set Excel 2007 to automatically save files in Excel 97-2003 format: Start up Excel 2007. Open an empty worksheet.

Excel tips! Two-minute tips from David Carter

Here are four quick Excel tips, each of which you can master in two minutes or less! These tips are courtesy of Excel guru David Carter and our sister site, Accountingweb.co.uk.Excel 2min Tip #1: Sort data with the =RIGHT function: by David CarterWhen Excel sorts a column of items, it looks at the initial character of each item, that is, the characters to the LEFT. But suppose you want to sort according to characters on the right? For this you need to use the =RIGHT function.

SumIf's little known function

SumIf is a great function that a lot of Excel users don’t know up about. Basically it combines two very popular functions - the Sum function and the IF Function. SumIf tests specified cells and if those cells meet certain conditions or “criteria” then selected cells are summed up. Think of the possibilities: You may wish to sum up receipts by different lockboxes or sum up the value of past-due invoices. Or you may just want to add up negative numbers in a column quickly or add up the returns or overtime for a particular day. I am using a very simple example to illustrate SumIf.

Embedding Data from an Excel Spreadsheet in a Power Point Presentation

Embedding data from an Excel spreadsheet into a Power Point presentation is especially useful if the basic presentation is used repeatedly and frequently. When data is embedded in a presentation or document it allows the data in the presentation to be updated automatically when the data is updated in the source spreadsheet. Presuming both the source spreadsheet and the destination presentation already exist and are open in separate windows, follow these steps to embed the data: Select the range of cells you want to copy in the Excel spreadsheet.


Already a member? log in here.