Archives for January 2012

How to use Wrap Text and Merge Cells Formatting to Improve Excel Worksheet Appearance

Wrap Text for Labels

Wrap Text for Labels

In this tutorial – the second in my series explaining how a worksheet, that my accountant friend Alan Friedman created, works – I focus on improving the formatting for the column labels and other descriptive text.

Wrap Text for Labels

In my experience, I find that many accountants and other financial managers have not mastered the basics of formatting an Excel Worksheet. It seems that they are still using “old fashioned IBM typewriter tricks” when it comes time to create the labels for the columns of data in their spreadsheet.

Case in Point: Using two or more vertical cells to type “Cost of” (Return key on Computer) “Goods Sold.” This “old fashioned” process will lead to multiple problems. For example, improper sorting and filtering of data.

In this tutorial, I demonstrate how to use the Wrap Text command to format a single-cell text label. You will also learn how to use the Keyboard Shortcut Alt + Enter to control where your text “breaks” when wrapping text.

Merge Cells Across Columns

When you have a long, descriptive text – e.g. Your Company Name – it is best to type this into a single cell and then use the Merge Cells Command. Now, when you double-click between Column Letters to automatically expand the width of a column, only the “um-merged” labels in the column are extended in width.

Additional Resources for Tutorial

You can learn “The 50 Best Tips for Excel 2007” on my DVD-ROM. Visit – http://shop.thecompanyrocks.com/50-best-tips-for-excel-2007/ – to learn more about this.

Click on this link to download the Excel Worksheet – created by Alan Friedman – that I use in this lesson.

Watch Tutorial in High Definition

Follow this link to watch this tutorial in High Definition on my YouTube Channel – DannyRocksExcels

View Tutorial Now

How to Apply Alternate Row Shading Using Conditional Formatting in Excel

Alternate Row Shading

Alternate Row Shading w/ Conditional Formatting

This is the first tutorial in a new series. In collaboration with my good friend, Alan Friedman – CPA and partner with the firm Friedman, Kannenberg & Co., PC – I am using Alan’s Financial worksheets to demonstrate how to get the most out of Excel. In this lesson, I show you how to apply Alternate Row Shading – aka “Green Bar Shading” – to a Year-end Inventory Analysis Excel worksheet.

Conditional Formatting in Excel

In this lesson, I create a new rule that uses a formula to determine the “condition” – or criteria – that must be met in order to apply the “formatting” to my selection of cells. The “key” to understanding this rule: The formula that you use for this “rule” must return either TRUE or FALSE. The cells – in the selection – that evaluate to TRUE will receive the special formatting – a background shading.

Extending Range of Cells to Receive Conditional Formatting

In general, you can use Excel’s AutoFill Handle to extend Conditional Formatting to adjacent cells. If, however, you want to apply the “Rule Using a Formula” to non-adjacent cells, first copy a cell that contains the Conditional Formatting. Then, use Paste Special – Formatting to apply this rule to your new selection of cells.

Resources for This Tutorial

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to preview all of the resources that I offer to you.

Please visit the Friedman, Kanneberg & Co. website to download the worksheet used in this series of lessons – and other seminar handouts.

 

Watch Tutorial in High Definition

Follow this link to watch this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Play Video Now

How to Create Custom Views of a Worksheet in Excel

Custom Views in Excel

Custom Excel Views

This is Part 2 in my series of video tutorials demonstrating the Commands found on the View Tab of the Excel Ribbon. Building on the concepts that I demonstrated in Part 1 (“How to Freeze Row and Column Labels While Scrolling in Excel”), I now show you how to save these settings as a Custom View.

Create a Custom View in Excel

  1. Display the settings that you wish to save as a Custom View – e.g. Changing the ZOOM Level of Magnification, Freezing Rows or Columns, etc.
  2. From the View Tab on the Ribbon, choose the Custom Views Command.
  3. In Custom Views Dialog Box, click Add; Give your View a Name and Click OK.
  4. Remember to Save your Excel Workbook. To test your custom view, I recommend that you revert to your normal or default view and save that version. Then close the workbook and reopen it. Now, it will display the last view displayed when you saved the workbook. Click on the Custom Views Command and select the Custom View that you recently added; the Custom Settings will now display.
  5. Add – and Save – additional Custom Views.

Custom Views are Worksheet Level Views

When you create a Custom View, it only applies to the Excel Worksheet where you created it. In fact, while you “Show” a Custom View, all other Worksheets in the Active Workbook are NOT available.

Excel Tables and Custom Views

 There is one “gotcha” with Custom Views. If you have formatted a data set as an Excel Table on ANY worksheet in the workbook, ALL Custom Views are blocked out. Watch  this video to see how to “work around” this roadblock.

Online Shopping at The Company Rocks

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to preview all of the resources that I offer you.

Watch Tutorial in High Definition

Follow this link to view this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Play Video Now


 

 

How to Freeze Row and Column Labels While Scrolling an Excel Worksheet

Split Panes on Worksheet

Split Panes on Worksheet

This is Part 1 in a multiple part series of tutorials. I am responding to the requests of several viewers who want to know the best ways to view an Excel Worksheet.

Freeze Panes and Split Bars

In this tutorial, I demonstrate how to use the three commands on the Freeze Pane Menu:

  1. Freeze Top Row
  2. Freeze Panes – Rows above and Columns to the left of the Active Cell
  3. Freeze First Column

When you need more flexibility to control the visible panes on your worksheet, use the Split Bars. I show you how to apply, modify and remove Split Bars – Vertical and Horizontal – on your worksheet.

With the Split Bars, you gain additional Navigation Scroll Bars – One above the Split and one below the Split with a Horizontal Split Bar. And, one to the left and one to the right of the Vertical Split Bar.

Go to Additional Tutorials in this Series

Visit My Online Shopping Website

I invite you to visit my secure online shopping site – http://shop.thecompanyrocks.com – to preview all of the resources that I offer you.

Watch Tutorial in High Definition

Follow this link to watch this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Watch Video Now

Click below to start watching this tutorial now.

 

How to Clear All Underlying Cell Formatting in Excel

Clear Underlying Formatting

Clear Underlying Formatting

Have you ever typed data or a formula in a cell only to be surprised by the result? For example, you type in a number and then, unexpectedly,  Excel displays a Date or a Time in that cell. This happens frequently when you reuse an older Excel Worksheet of if you start working on a worksheet that a colleague or client has sent you.

Why does this happen?

Clear All Underlying Cell Formatting

In my experience, I have found that many Excel users mistakenly think that pressing the DELETE Key will “erase” the cells in the selection. No, No, No. The Delete key only erased the cell content – the data, text or formulas entered or pasted into the cell(s). The Delete key does NOT remove the Underlying Cell Formatting!

In this tutorial, I demonstrate how to remove the underlying cell formatting while leaving the cell Contents in place.

Topics Covered in this Tutorial

  • Using the Clear Formatting Command
  • Adding the Clear Command to your Quick Access Toolbar
  • Using Go To Special to Find and Select all cell that use either Conditional Formatting or Data Validation
  • Finding the Last Cell in your worksheet – you may be surprised to find a “remote cell” is NOT Blank!

View All the Resources that I Offer

I invite you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to preview all of the resources that I offer.

Watch this Tutorial in High Definition

Follow this link to view this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels