# Excel tips

Technology

## Sales/Tax by shipping state in Excel – The power of pivot tables, Part 1

This is the first of two articles wherein Chuck Vigeant demonstrates several ways of approaching Sales and Tax by Shipping State within Excel – one without a pivot table, and one with a pivot table.
Technology

## Sales/Tax by shipping state in Excel – The power of pivot tables, Part 2

This is the second of two articles wherein Chuck Vigeant explores the ability to generate a Sales by Shipping State report in Excel.
Technology

## Transform multiple-line cell contents into columns in Excel

Sometimes in Excel you may encounter a situation where each cell contains multiple lines of data. In this article I'll explain some simple techniques that can make quick work of transforming this data into columns.
Technology

## Understanding the Undo Option in Excel

One of my favorite features in Excel is the Undo feature, which as you might expect, allows you to undo the last action that you carried out in Excel. Indeed, you can generally undo multiple steps in Excel. However, there are some caveats to this functionality, as well as a couple tricks you may not know.
Technology

## Excel Tip: Converting Numbers Formatted as <1> to -1

In this article I'll discuss two approaches you can use to convert text-based inputs to negative numbers. I'll also share a formatting technique you can use if you want to enclose negative numbers in such brackets instead of parentheses.
Technology

## What's the FREQUENCY? Using Excel's FREQUENCY function

From time to time you may want to determine how items fall within specific ranges. For instance, a real estate portfolio manager may wish to know how many buildings fall within ranges of 250,000, 500,000, and one million square feet.
Technology

## Some unlikely uses for Excel's SUMPRODUCT function

While you may not be aware of Excel's SUMPRODUCT function, those in the know often use it for its stated purpose: multiplying two ranges together and then summing the total. Although this is helpful, you might not be aware that SUMPRODUCT also can function like VLOOKUP or SUMIF on steroids.
Technology

## Three tricks for formatting rows in Excel

I'm sure that you've worked with data sets in Excel where the information starts blurring together. In this article I discuss several techniques that I use avoid getting lost in the forest.
Technology

## Three Excel Menus You May Have Overlooked

By David H. Ringstrom, CPASheet NavigationEveryone is familiar with using the arrow buttons in the lower left-hand corner of the Excel screen to push worksheet tabs back and forth across the screen. However, try right-clicking on these buttons instead, and you'll see a menu similar to Figure 1. Simply click on a sheet name to instantly activate that worksheet.&nbsp;
Technology

By David H. Ringstrom, CPAAdd-ins are programs that plug-in into Microsoft Excel to add additional functionality. Some add-ins expand Excel's core functionality, while others allow third-party products to add features to Excel's environment. In this article I'll discuss how to enable some of Excel's hidden features, as well as give you some pointers on what to do when third-party add-ins vanish from Excel.Hidden FeaturesExcel ships with several add-ins that you can manually enable. Some of these include:&nbsp;
Technology

## Buried Gold: Excel's Form Command

By David H. Ringstrom, CPAMany users are not aware that Excel has long had a feature that enables you to browse through a table of data one record at a time. As shown in Figure 1, the Form command provides an easy way to navigate through a table of data. This command is easy to access in Excel 2003 and earlier versions, but is deeply buried in Excel 2007. In this article I'll show you how to use the Form command to make quick work of reviewing a list of records.&nbsp;
Technology

## Tricks for Opening and Closing Multiple Excel Files

By David H. Ringstrom, CPA
Technology

## Tricks for Hiding and Unhiding Excel Rows and Columns

Hiding and unhiding rows and columns are mundane tasks that many users take for granted. However, sometimes simple tasks can trip up Excel users, like unhiding just one row or column within a hidden set.
Technology

## Excel Text Transformation Tricks

By David H. Ringstrom, CPAData arrives on your desk from multiple sources, and in many cases it's not quite in the format that you need. For instance, a user may have left the CAPS LOCK on while typing in a series of addresses. Or you've exported data from a program that pads fields with spaces at the beginning or end. Fortunately, there's no need to retype the list. Excel offers four easy-to-use functions that can quickly transform data into just the format that you need.
Technology

## Excel 2007: Five Ways to Streamline Excel’s User Interface

By David H. Ringstrom, CPAIt's easy to get caught up in the day-to-day use of Excel 2007 and overlook minor tweaks that can make a significant difference in your use of Excel. In this article I'll review five of my favorite Excel 2007 tricks. Most involve customizing the user interface so that I can speed through my work without hitting unnecessary bumps:1. Extend your recently used file listBy default Excel shows the 17 most recent files that you've opened, but as shown in Figure 1, you can expand this to a maximum of 50:&nbsp;
Technology

## Excel 2003: Six Ways to Streamline Excel's User Interface

By David H. Ringstrom, CPAIt's easy to get caught up in the day-to-day use of Excel 2003 and overlook minor tweaks that can make a significant difference in your use of Excel. In this article I'll review five of my favorite Excel 2003 tricks. Most involve customizing the user interface so that I can speed through my work without hitting unnecessary bumps or screen prompts. These tricks will work equally well in Excel 2002, but only some apply to Excel 97 and 2000:1. Extend your recently used file list
Technology

## The far side of Excel - Fireside treats for long winter nights

As you sit by your roaring log fire, idly fondling the shiny new notebook computer that Santa bought you, here are some suggestions for trying out a few lesser known features that lurk in the depths of the Microsoft Office applications, courtesy of our sister site, AccountingWEB.co.uk.Microsoft Word - Excel in disguiseFor most accountants the real problem with Word is that it isn't Excel.
Technology

## 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.
Technology

## Excel Tip: Hide an Excel Worksheet in Plain Sight

By David Ringstrom, CPAAs you&rsquo;re probably aware, it&rsquo;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&rsquo;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.
Technology

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.

## Voice of the Editor

Hi. I’m Caleb and I’m new around here.

Which isn’t completely true. I mean, occasionally I drop by when I manage to sneak out of the nonstop frat party over at Going Concern, but I’m mostly a wallflower over there. I’m happy to say that I’ve been given express permission (or explicit orders, if you like) to wander over here to AccountingWEB more often.

Why is that, you might ask? My job is to replace the irreplaceable Gail Perry as Editor-in-Chief. What does that mean? I don’t really know! I think it’ll be fun getting a feel for things, throwing in my own thoughts here and there, and listening to the discussions you’re having about the accounting profession.