Search

Excel Tricks
11
Jun

Hiding and Unhiding Multiple Sheets without using Macros
  • David Ringstrom, CPA
  • 2249
One of the first “tricks” an Excel user learns is to hide and unhide a sheet. This is an exceptionally useful feature as it allows us to store data in a sheet, such as lists and tables, but keep the user of the workbook from seeing, manipulating, and more importantly, corrupting the information on the hidden sheet. As with most things in Excel, there is more than one way to hide a sheet or multiple sheets.  One of the easiest methods is to select a sheet (or select multiple sheets using standard Windows CTRL and Shift selection techniques), right-click the sheet tab then select “Hide”. You can hide a worksheet and the user would not see it when he/she opens the workbook. However, they can easily unhide the worksheet if they want (as we will see later in this tutorial). But what if you don’t want them to be able to unhide the worksheet(s).Let’s see how to hide a worksheet in Excel so that it can easily be unhidden, or can not be unhidden.
  • Read More
19
Apr

Maximize QuickBooks: Excel Analysis
  • David Ringstrom, CPA
  • 438
When it’s time to crunch numbers in QuickBooks, most users rely on reports within the software, while others turn to analytics programs. In either case, you may still sometimes need to analyze your accounting data in new ways. Pivot tables can make quick work of figuring out patterns or issues with your data that might be hard to discover otherwise. Pivot tables allow you to create instant reports and/or charts by dragging and dropping fields with your mouse.This means you can quickly rearrange even enormous reports into interactive, yet compact summaries.It’s almost a given that small to mid-sized businesses (SMBs) use QuickBooks to handle their accounting. The combination of easy-to-use and power is hard to beat.For some businesses, the built-in reports are all the information they ever need. For others, additional analysis is needed to do things like aggregate based on item type, location, address, or customer. One of our early users wanted to see the total amount of each product type they shipped to each state. Nothing crazy, but not something that QuickBooks just gives you.Doing this sort of analysis in Excel is certainly possible. Generally, it involves some copying and pasting followed by a pivot table. To see a great how-to for doing it with Excel, check out this blog by David Ringstrom:Attend this on-demand webcast "
  • Read More
26
Mar

Repairing Damaged Excel Workbooks
  • David H. Ringstrom, CPA
  • 3457
There is never a good time to encounter the prompt that informs you that the workbook you’ve opened is damaged and cannot be opened. When this happens, the first thing to do is stop and take a breath. All may not be lost with your file. In this article I’ll lay out a couple things to try before you give up. Y If you see this prompt, the first thing to do is to press Enter to accept the default choice of Repair, or else click Repair with your mouse. This often clears up many issues with Excel workbooks. In fact, I often use this process on files that are not necessarily outright corrupted, but feel like something isn’t right:Within Excel’s Open dialog box click on the workbook that you wish to repair.Click the arrow at the right of the Open button. Choose Open and Repair from the menu that appears.Click Repair from the prompt. 
  • Read More
11
Mar

Clearing Numeric Input from an Excel Spreadsheet
  • Allen Smith, CPA
  • 2319
Erasing manual inputs from a spreadsheet that you want to reuse can be a tedious task. Despite our best intentions, entries often get saved into our master copies of spreadsheets that we reuse. Fortunately, you don’t have to go cell by cell to erase inputs. Specifically, we’re going to look at erasing numeric inputs, although you may be able to alter the technique to replace text-based inputs:Select the cells that contain the data you want to remove, which can include numeric inputs, text, and formulas. To select an entire worksheet, click the top left corner of the worksheet frame, otherwise manually select two or more cells.Activate Excel’s Home menu.Choose Find & Select.Choose Go To Special. Choose Constants.Clear the checkboxes for Text, Logical, and Errors, leaving only Numbers selected. Click OK. Press the Delete key to clear the cells but keep the formatting intact. If you wish to completely clear the cells, choose Clear and then Clear All on the Home menu instead.
  • Read More

Connect with Us

Copyright © 2024 Excel Accountant. All Rights Reserved.