Excel tips

Excel Tip: Change Formula to Actual Value

Is your formula your private property? Are there times when you provide spreadsheets for others to view but you don't want to display the underlying calculations? Once you've created a worksheet and performed all the calculations, you can switch the calculations to the calculated values so only the final numbers appear, both in the work area of spreadsheet and in the formula bar at the top of the spreadsheet.Change the contents of a cell or group of adjacent cells from formula to value by following these steps:1.

Excel Tip: Ten Tips for Working With Multiple Sheets

There are several tricks and shortcuts you can employ when you want to use multiple sheets within one workbook. Here are a few of our favorites - feel free to add a comment and share your own tricks with the crowd!Insert new sheets.Are you unhappy because only three sheet tabs appear at the bottom of your screen? Choose Insert, Worksheet from the menu and add another (and another, and another…). To speed things up a bit, click on Sheet1, hold down Shift, click on Sheet3 and three sheets are selected.

Excel Tip: Consolidate your Spreadsheets Pivot Tables

Manipulating multiple worksheets is made easy with our latest pivot table tutorial from pivot king David Carter.His latest guide - Consolidating multiple worksheets in a Pivot Table - to this invaluable Excel tool should take only 15 minutes, and shows you how to take a number of worksheets and turn them into one comprehensive spreadsheet.Carter's new tutorial comes in response to a question from AccountingWEB reader Tom Trainer, who wanted to know if pivot tables could be used to

Excel Tip: Automating the Presentation of a List

Are there lists that you need to enter over and over again in your Excel worksheets? For example, do you frequently prepare worksheets containing a list of the chart of accounts, your employees' names, your inventory parts, and so on? You might uncover an older worksheet file that contains the list, copy the original list and paste the list into your new file. Or perhaps you retype the list each time you need it to appear in your worksheet.Here's a quick and effective method for saving and recalling lists.

Excel Tip: Manipulating 3-D Worksheets

For those of you who have an interest in working with multiple spreadsheets within a single Excel file, here's a quick tip for manipulating numbers in a situation where you have several sheets of identical format, but with different numbers. Say you have 12 active sheets, with sheet 1 as the summary sheet and sheets 2-11 providing the detail, and you want to add the contents of Cell D5 from sheets 2-11 and place the sum in cell D5 of sheet 1. Step 1: Place your cellpointer in cell D5 of sheet 1 and initiate the formula by clicking the AutoSum button on the standard toolbar.

Excel Tip: Change Excel Default Settings

If you find that every time you create an Excel worksheet you need to change settings, such as margin, number format, typeface, footer, column width, etc., and these changes are the same for each of your worksheets, you may want to simply make changes in the new worksheet defaults.To create new default settings in Excel, follow these steps:1. Open a blank workbook.2. Without entering information in the cells, make all the formatting adjustments that you want to incorporate into your future workbooks.3. Choose File, Save. The Save As window will appear.4.

Excel Tip: Alphabetizing a Column of First and Last Names by Last Name

How many times have you entered a list of names, first and last name in the same column, like this:Joe SmithMargaret HowardSherry AndersonMichael WilsonMeredith Morganand so on……only to find that you need to alphabetize the list by last name. Do you have to re-enter the entire list so that the last name appears first? Here's a quick method for sorting a list like the one above by last name.The process involves separating the text into two columns, one for the first name and one for the last name.
Technology

Word/Excel Tip: Resize Toolbar Text Buttons & Boxes

If you need to make room for additional buttons, you may want to decrease the size of some of the toolbar boxes, such as the Zoom, Font or Style boxes. Or, perhaps you'd like to increase the size in order to see the entire entry of each option in a dropdown list.To change the width of a toolbar text button, text box or list box, choose Tools/Customize to display the Customize dialog box and enable toolbar editing.Next, click on the toolbar box you wish to resize to select it.

Excel Tip: Quickly Print Designated Areas of a Worksheet

Set up your various print ranges first by using your mouse to select a range, then assign a name to the range. Assign a range name by choosing Insert, Name, Define from the menu. In the Names in workbook field, enter the name you want to use to identify this selected range. You cannot use spaces in your range name. Click OK to complete the process. Assign other named ranges in your worksheet in the same manner.

Excel Tip: Automating the Filepath on a Spreadsheet

On the surface it would seem that entering the name of your path and file in the footer (or header) of an Excel worksheet is not an automatic operation. This is such a frequently-requested task, you would think that there would be a quick menu choice to perform this duty. But there is not. In the absence of such a menu selection, here is a quick little macro that you can place in your worksheet that should do the trick. Choose Tools, Macro, Macros from the Excel menu.

Excel Tip: Modelling and What-If? Analysis With Pivot Tables

This is one in a series of Excel Pivot Table Tutorials developed by AccountingWEB software consultant David Carter.
Technology

Excel Tip: Correct Erroneous Dictionary Entries in Word and Excel

Have you ever used the spell-checker in Word or Excel and clicked the Add button when you meant to click Change or Ignore? Suddenly a misspelled word has been added to your dictionary, and from now on, every time you type "acounting" instead of "accounting," the spell-checker blithely accepts your typing and ignores your shouts and pleas to correct the spelling of the word. All words that you add while using the spell-checker in any of the Microsoft Office programs (including Word and Excel), are saved in a file called Custom.dic.

Budgeting With Pivot Tables - Part II

This is one in a series of Excel Pivot Table Tutorials developed by AccountingWEB software consultant David Carter.

Excel Tip: Top 20 Productivity Points

AccountingWEB's latest Excel guru Chris Bales has put together his Top 20 Excel Productivity Points - a must for all Excel users.Useful tips include advice on hardware, software, worksheets, and troubleshooting. Print out this essential guide and you can make the most of Microsoft's popular spreadsheet tool.Chris Bales' Top 20 Excel Productivity PointsHardwareFrequently overlooked, hardware setup can make a big difference, but the power of the computer is not that important for Excel in everyday use.

Excel Tip: Audit & Financial Reporting Using Pivot Tables

This is one in a series of Excel Pivot Table Tutorials developed by AccountingWEB software consultant David Carter.

Excel Tip: Pivot Table Tutorial: Data Extraction

This is one in a series of Excel Pivot Table Tutorials developed by AccountingWEB software consultant David Carter.

Analyze Sales Data With Excel Pivot Tables

This is one in a series of Excel Pivot Table Tutorials developed by AccountingWEB software consultant David Carter.

Excel Tip: Budgeting With The Pivot Table

This is one in a series of Excel Pivot Table Tutorials developed by AccountingWEB software consultant David Carter.
Technology

Excel Tip: Secrets Revealed in Pivot Table Tutorial

Consultant David Carter has created an exclusive tutorial for AccountingWEB showing how Excel pivot tables can be used to create budgets.The tutorials are a new breakthrough in online learning on AccountingWEB and can be downloaded here to let users work through them at their own pace.To launch his series of pivot table tutorials, David Carter discussed in a recent workshop how pivot tables applied to all manner of financial reporting tasks

Pages