Excel Basics | The Company Rocks

Danny February 2, 2012 Leave a Comment

The Basics for Creating and Copying Formulas and Functions in Excel

Relative and Absolute Cell References

Formulas and Functions are the “core elements” of Excel. It is vital that you have a solid grounding in understanding how formulas and functions work; especially when you need to copy and paste them into other cells.

Relative and Absolute Cell References

When you use Relative Cell References – the default setting in Excel – the Row numbers and Column letters adjust automatically when you copy and paste a formula.

There are, however, situations where you need to “freeze in place” part of an Excel Formula. For example, you need to “freeze” or use an Absolute Cell Reference to the cell with “Total Sales,” when creating and copying a formula to determine Product Sales as a Percentage of Total Sales.

Copying Excel Formulas

In this tutorial, I demonstrate two methods for copying and pasting formulas and functions:

Standard Practice is to select the cell with the formula and use the Ctrl + C Keyboard Shortcut to place the formula cell on the Excel clipboard. Then, after selecting the destination cell(s), use the Ctrl + V Keyboard Shortcut to paste the formula in the new location(s)
AutoFill Tool.If you are copying the formula cell into adjacent cells, use the AutoFill tool to do this quickly and accurately!

Tips that You May Not Know

In my experience, many Excel veterans are not familiar with these tips and tricks which I demonstrate in this tutorial:

The Ctrl + ~ (tilde) Keyboard Shortcut to “toggle” the Show Formulas view for the active Excel Worksheet.
The Alt + Enter Keyboard Shortcut to automatically use the =SUM() Function – for adjacent cells.
The F4 Key to automatically add Absolute Cell Reference when creating or editing a formula. For example, converts A1 to $A$1.

Learn More Excel Tips and Tricks

50 Best Tips DVD-ROM

If you enjoy the tips and techniques that I demonstrate in this lesson, then you will really benefit from purchasing my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007.” You can learn more about the resources that I offer by visiting my secure online shopping website – http://shop.thecompanyrocks.com

Watch Tutorial in High Definition

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

Play Video Now

Share and Enjoy:

Filed Under: Excel Basics, Excel Formulas, Excel Functions, Excel Training, YouTube Videos by Danny Rocks Tagged: Absolute cell reference, AutoFill Options, AutoSum, Copy Excel Formulas, Excel Formulas, Excel Functions, Excel Keyboard Shortcuts, Excel Video Training, Keyboard Shortcuts in Excel, Learn to use AutoFill in Excel, MS Excel Training, Operators in Excel Formulas, Relative Cell Reference, Show Formulas in Excel, SUM Function in Excel, Tips and Time Savers, Video lessons for Excel

Danny January 24, 2012 3 Comments

How to Create Custom Views of a Worksheet 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

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.
From the View Tab on the Ribbon, choose the Custom Views Command.
In Custom Views Dialog Box, click Add; Give your View a Name and Click OK.
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.
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

Share and Enjoy:

Filed Under: Excel Basics, Excel Training, MS Excel Training, Workbooks and Worksheets in Excel, YouTube Videos by Danny Rocks Tagged: Custom Views in Excel, Excel Tables, Excel Video Training, Freeze Panes in Excel, MS Excel Training, Video lessons for Excel

Danny January 17, 2012 1 Comment

How to Freeze Row and Column Labels While Scrolling an Excel 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:

Freeze Top Row
Freeze Panes – Rows above and Columns to the left of the Active Cell
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

How to Create Custom Views for Worksheets in Excel

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.

Share and Enjoy:

Filed Under: Excel Basics, Excel Training, YouTube Videos by Danny Rocks Tagged: Excel Video Training, Freeze Panes in Excel, Navigational Scroll Bars, Scroll Bars in Excel, Split Panes in Excel, Video lessons for Excel

Danny January 12, 2012 2 Comments

How to Clear All Underlying Cell Formatting in Excel

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

Share and Enjoy:

Filed Under: Consolidate Excel Data, Data Validation, Excel Basics, Excel Training, Format Cells in Excel, YouTube Videos by Danny Rocks Tagged: Clear Formatting from Cells in Excel, Conditional formatting in Excel, Customize Quick Access Toolbar, Data Validation, Excel Video Training, Find Last Cell Command, Go To Special dialog box, Video lessons for Excel

Danny December 7, 2011 Leave a Comment

How to Distinguish Cell Content from Cell Formatting in Excel

Cell Content vs. Cell Formatting

Many Excel Users are surprised to discover that the Delete Key does not clear the formatting for the selected cell or range of cells. This can cause some unusual “surprises” you you or one of your colleagues later uses this “Deleted” Cell Range!

On the other hand, some cells must be formatted properly in order to return the correct result. This is especially true for cells that contain Time and Date Calculations. For example, in order to properly calculate a Sum of Hours that exceeds 24 hours, you need to apply the Custom Format [h]:mm to the formula cell.

Topics Taught in this Tutorial

Adding the Clear Command to the Quick Access Toolbar (QAT).
Using Go To Special – Last Cell, to find the last cell in the worksheet that contains either content or formatting – or both!
Understanding how Excel Stores Dates (as a Serial Number) and Times (as a Decimal Number) vs. How you use Cell Formatting to make Dates & Times easier to understand.
Using Keyboard Shortcuts to add the Current Date – Ctrl + ; (semi-colon) or Current Time -Ctrl + Shift + : (colon), or to “Toggle” between Formulas and Displayed Results -Ctrl + ~ tilde).

Visit My New, Secure Online Shopping Website

Stop by http://shop.thecompanyrocks.com to see the new products that I have created. You can also download – for FREE – my Chart of Popular Excel Keyboard Shortcuts when you visit.

Watch Tutorial in High Definition

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

YouTube Tutorial for this Post

Share and Enjoy:

Filed Under: Excel Basics, Excel Training, Format Cells in Excel, YouTube Videos by Danny Rocks Tagged: Clear Formatting from Cells in Excel, Customize Quick Access Toolbar, Excel Keyboard Shortcuts, Excel Video Training, Format Cells in Excel, Go To Special dialog box, Video lessons for Excel

Danny September 6, 2011 Leave a Comment

Keyboard Shortcuts to Enter and Edit Data Efficiently in Excel

Keyboard Shortcuts to Enter Data

This is the latest installment in my series of video tutorials where I demonstrate how to use Keyboard Shortcuts in Excel. In this tutorial, I demonstrate how to Enter and Edit Data Efficiently in Excel using Keyboard Shortcuts.

Tips Covered on Video Tutorial

Here are the main Keyboard Shortcuts that I show you how to use on this video:

Enter Current Date in a cell.
Enter Current Time in a cell.
Use Ctrl + Enter to accept data entry and keep the focus on the current cell.
Use Ctrl + Enter to populate a range of selected cells.
Use Alt + Enter to “Wrap the Text” in a cell.
Use F2 to edit data inside a cell.
Use Ctrl + left & right directional arrows to move to the beginning of the next / previous word while editing in the cell.
Use ‘ (apostrophe) as the first character in a cell to make this a “text” entry.

Download my complete Chart of Popular Keyboard Shortcuts as a PDF

Follow this link to download – for free – my 5 Page Chart of Popular Excel Keyboard Shortcuts as a PDF.

Subscribe to my Video Podcast on iTunes

Here is the link to subscribe to my video podcast, “Danny Rocks Tips and Timesavers” on iTunes. It is offered for free!

Watch Tutorial in High Definition on YouTube

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

Share and Enjoy:

Keyboard Shortcuts to Enter and Edit Data Efficiently in Excel [ 11:43 ] Play Now | Play in Popup | Download (1304)

Filed Under: Excel Basics, Excel Training, Keyboard Shortcuts, Podcast Tips & Timesavers Tagged: Enter Dates in Excel, Enter Time Values in Excel, Excel Keyboard Shortcuts, Excel Video Training, Tips and Time Savers, Video lessons for Excel, Wrap Text in a Cell

Danny August 23, 2011 Leave a Comment

Excel Keyboard Shortcuts to Hide, Unhide, Insert and Delete Cells, Columns and Rows

Commands in Cells Group

Here is the latest installment in my series of video tutorials demonstrating how to use Keyboard Shortcuts in Excel. These Keyboard Shortcuts perform actions that you find in the Cells Group of commands on the Ribbon in either Excel 2007 or Excel 2010.

Topics Taught in Video Tutorial

Here are the topics that I teach in this tutorial. Using Keyboard Shortcuts to:

Select all cells in current row(s) Shift + Spacebar
Select all cells in current column(s) Ctrl + Spacebar
Open Insert Dialog Box Ctrl ++ (plus)
Open Delete Dialog Box Ctrl + – (minus)
Open short / contextual menu Shift + F10
Hide current row Ctrl + 9
Hide current column Ctrl + 0 (zero)

Download My “Chart of Popular Keyboard Shortcuts for Excel”

Click here to go to my Exclusive Membership Content page and download my charts of keyboard shortcuts. They are free to members who register.

Watch Video in High Definition

Follow this link to view this tutorial on my YouTube Channel – DannyRocksExcels

For some strange reason, I could not produce this video for my Podcast. So, the only way to view it is on YouTube.

Puchase My DVD-ROM

If you enjoy these tips. If you enjoy my style of instruction. Then, you will really benefit from purchasing and learning from my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007.”

Follow this link to learn more about my DVD-ROM and to open a Secure Shopping Cart to make your purchase.

Share and Enjoy:

Filed Under: Excel Basics, Excel Training, Keyboard Shortcuts, YouTube Videos by Danny Rocks Tagged: Clear Formatting in Excel, Contextual Menu in Excel, Delete Cells in Excel, Excel Keyboard Shortcuts, Excel Short Menu, Excel Video Training, Hide Rows and Columns in Excel, Insert Cells in Excel, Insert Rows or Columns in Excel, Unhide Rows and Columns in Excel, Video lessons for Excel

Danny August 22, 2011 Leave a Comment

How to Use Excel Keyboard Shortcuts to Cut, Copy and Paste Formulas and Formatting

Keyboard Shortcuts for Cut, Copy & Paste

In addition to showing you the Keyboard Shortcuts to Cut, Copy and Paste in Excel, I demonstrate how these actions affect formulas and formatting.

Topics in this Tutorial

Here are the topics that I demonstrate in this video tutorial:

Select multiple cells using the keyboard
Use Ctrl + Enter to update all cells in the selection
Use Ctrl + ~ to “toggle” between showing and hiding formulas in all cells
Use F2 to edit in the cell – and to diagram cell references for formulas
Use Ctrl + C to copy the “relative cell references” in a formula
Use Ctrl + X to cut an Excel formula that retains the original cell references
Open and use the Office Clipboard to store up to 24 most recent copy and cut items
Use Ctrl + Z to undo multiple actions
Use Ctrl + A to select all cells in a contiguous range
Use the Delete key to delete the contents of a cell or range while retaining the formatting
Use the Clear All command to remove both formatting and contents in a range of cells

Download My Complete Charts of Popular Keyboard Shortcuts

Follow this link to my Excelusive Membership Content Page where you can download all of my charts and tables of Keyboard Shortcuts.

Watch Video in High Definition

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

Share and Enjoy:

How to Use Excel Keyboard Shortcuts to Cut, Copy & Paste Formulas & Formatting [ 11:59 ] Play Now | Play in Popup | Download (1267)

Filed Under: Excel Basics, Excel Training, Format Cells in Excel, Keyboard Shortcuts, Podcast Tips & Timesavers Tagged: Clear Formatting from Cells in Excel, Copy Formulas in Excel, Excel Keyboard Shortcuts, Excel Video Training, Office Clipboard, Select Multiple Cells in Excel with Keyboard Commands, Tips and Time Savers, Video lessons for Excel

Danny July 10, 2011 Leave a Comment

My Two Most Popular Excel Tutorials on YouTube

YouTube - DannyRocksExcels

Three years ago – in 2008 – I started to create Excel Video Tutorials, both for this website and, also, for my YouTube Channel – DannyRocksExcels.

Looking back, I am amazed to see that two of my earliest postings have been viewed over 50,000 times on YouTube!

Improved Production Values Since 2008

While I may sometimes “cringe” at my production value choices from 2008, I must say that the content of each of these two viewo tutorials holds up nicely!

What I Offer Today!

I have come a long way since 2008 – both in my knowledge of Excel and How to present the Excel Training Options that you can benefit from.

Re: Excel Pivot Tables – Paradoxically, Pivot Tables are BOTH the most powerful tool in Excel AND also, one of the easiest tools to use to analyze and present the information hidden inside your data!

Here is a link to the Excel Video Tutorial Recordings that you can download for only $9.95 USD. I have customized each recording for Excel 2003, Excel 2007 and Excel 2010!

Get my DVD-ROM – “The 50 Best Tips for Excel 2007” for only $29.97!

Let me know what you think!

Danny Rocks

The Company Rocks

Share and Enjoy:

Filed Under: Excel Basics, Excel Training, Pivot Tables, WebEx Recordings & Webinars, YouTube Videos by Danny Rocks Tagged: Data entry errors in Excel, Excel Pivot Tables, Excel Video Training, Source Data for Pivot Tables, Video lessons for Excel

Danny June 29, 2011 Leave a Comment

Working with Custom Lists in All Versions of Excel

Edit Custom Lists in Excel

Custom Lists in Excel are great because the help to ensure accuracy and consistency when entering data on a worksheet. Excel comes with several Custom Lists built-in to the program – e.g. Days of the Week and Months of the Year. Now, you can quickly use Excel’s AutoFill handle to add Jan, Feb, Mar, Apr, etc. in any direction (vertical or horizontal) on the active worksheet.

Edit Custom Lists

You can quickly create – or edit – your own Custom List. Follow these steps:

Type your list in a contiguous group of cells – either vertically or horizontally.
Select the cells with the values that you just entered.
Spell check this list – use the F7 Keyboard Shortcut
Open up the Edit Custom List Dialog Box. (Watch this video to see how this is differs between Excel 2010, Excel 2007 and Excel 2003.)
With your new Custom List selected, click the “Import” button to add your Custom List to the current version of Excel on this computer.

Sort Data Using a Custom List

On this video tutorial, I demonstrate how to sort a list of data using a Custom List – e.g. to get the “Month” field sorted in chronological order (January, February, etc.) This will save you a great deal of time!

Create a Custom List for Letters of Alphabet

Having a Custom List for the 26 letters of the alphabet comes in handy on many occasions. I show you how to AutoFill down the initial list using =Char(Row() + 64) beginning in Row 1 – a really usefuly function!

View This Excel Video Tutorial in High Definition

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

Get my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007” for only $29.97!

Share and Enjoy:

Working with Custom Lists in All Versions of Excel [ 8:03 ] Play Now | Play in Popup | Download (1477)

Filed Under: Excel Basics, Excel Functions, Excel Training, Tips and Time Savers Tagged: CHAR() Function in Excel, Create Custom List in Excel, Custom Lists, Excel Video Training, Learn to use AutoFill in Excel, ROW() Function in Excel, Sort using an Excel Custom List, Tips and Time Savers, Video lessons for Excel

Danny Rocks “Training You to Excel”

Follow Danny on Social Media

Relative and Absolute Cell References

Copying Excel Formulas

Tips that You May Not Know

Learn More Excel Tips and Tricks

Watch Tutorial in High Definition

Play Video Now

Create a Custom View in Excel

Custom Views are Worksheet Level Views

Excel Tables and Custom Views

Online Shopping at The Company Rocks

Watch Tutorial in High Definition

Play Video Now

Freeze Panes and Split Bars

Go to Additional Tutorials in this Series

Visit My Online Shopping Website

Watch Tutorial in High Definition

Watch Video Now

Clear All Underlying Cell Formatting

Topics Covered in this Tutorial

View All the Resources that I Offer

Watch this Tutorial in High Definition

Topics Taught in this Tutorial

Visit My New, Secure Online Shopping Website

Watch Tutorial in High Definition

YouTube Tutorial for this Post

Tips Covered on Video Tutorial

Download my complete Chart of Popular Keyboard Shortcuts as a PDF

Subscribe to my Video Podcast on iTunes

Watch Tutorial in High Definition on YouTube

Topics Taught in Video Tutorial

Download My “Chart of Popular Keyboard Shortcuts for Excel”

Watch Video in High Definition

Puchase My DVD-ROM

Topics in this Tutorial

Download My Complete Charts of Popular Keyboard Shortcuts

Watch Video in High Definition

Most Viewed Excel Videos on YouTube

Improved Production Values Since 2008

What I Offer Today!

Edit Custom Lists

Sort Data Using a Custom List

Create a Custom List for Letters of Alphabet

View This Excel Video Tutorial in High Definition

Locate My Video Tutorials by Category

Seach My Website with Keywords

Podcast Feeds

Blogroll

Blogs for Business Writing

Blogs for Excel

Blogs for Presentations

Register or Log In

Tags