Home Archives for Group edit in Excel
October 25, 2013 Leave a Comment

Working with Worksheets in Excel Workbooks – Part 1

I have been quite busy this year teaching several “continuing education” courses online for colleges here in the USA. One of the benefits – for me as the instructor – is that I get “real time” feedback from my students when they submit their assignments. I like this!

Objectives for Lesson - Working w Worksheets Part 1

Working with Excel Worksheets in Excel Workbooks

From some of my recent interactions with my students, I realize that I need to reinforce the concept of working with Excel Worksheets inside an Excel Workbook.

In this Excel Tutorial, I address these three concepts:

  1. 1) Working in “Group Mode” to place content and corrections in multiple Excel Worksheets.
  2. 2) The Advantages of using Color-Coding for each Excel Worksheet Tab.
  3. 3) Using the Keyboard Combinations of Ctrl + Page Up and Ctrl + Page Down to navigate – and “activate” individual Excel Worksheets inside an Excel Workbook.

Watch this Video in High Definition  on My YouTube Channel – DannyRocksExcels

Here is the link to watch this video on my YouTube Channel

Watch My Video Now in High Definition on YouTube

 

Link to this post!
Filed Under: Essential Excel Skills, Excel Training Tagged: , , , , ,
December 14, 2011 2 Comments

Use an Excel Template to Customize a Worksheet with 52 Weeks of Employee Time Cards

Template for Time Cards in Excel

Time Card Templates in Excel

This is Part Two in my new series of Excel Tutorials where I share my best practice tips for creating 52 weeks of Employee Time Cards – for hours worked. In this segment, I focus on starting the process with an Excel Template.

Finding Templates for Time Cards

As I demonstrate in the video, there are at least two easy sources for finding a Time Card Template:

  1. Sample Templates Installed on your Computer
  2. Templates that you can preview and download from the Microsoft Office Templates Website

Examining Templates that You Download

Before you even think about duplicating the Excel Template that you just downloaded, it is important for you to take a few minutes to explore:

  • The Formulas used in the Template
  • The Formatting used in the Template – especially the formatting for Dates and Times
  • The Protection Applied (if any) to the cells or the entire worksheet

Creating Drop-down Menus for your Template

Since our premise here is that we will copy the template to 51 additional worksheets, it makes sense to use Data Validation to create a series of “drop-down menus” for Employee names and the Start Date for each week. I demonstrate how to do this in the video tutorial.

Links to Additional Video Tutorials in this Series

Additional Resources for Excel

I invite you to visit my new, secure, online shopping website – http://shop.thecompanyrocks.com – to discover the many training 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

Click to Watch Video Now

Link to this post!
Filed Under: Data Validation, Excel Training, Names for Cells and Ranges, Work with Multiple Worksheets, YouTube Videos by Danny Rocks Tagged: , , , , , , , , ,
December 12, 2011 3 Comments

Tips for Creating 52 Weeks of Employee Time Cards in Excel

Insert & Copy Worksheets

Insert & Copy Worksheets

We are now winding down the end of the year 2011. Several of my viewers and clients have asked my for help in creating one Excel Workbook that will contain 52 weeks of employee time card information:

  • Name and Department of Employee
  • Hours Worked each day of the week – Regular and Overtime
  • Number of Holiday / Sick Days that were used / that remain, etc.

First Tutorial in this Series

Rather than trying to create each individual – and some are very specific – request, I have decided to combine multiple requests into a series of Best Practice Tips for my general audience to consider.

In this lesson, I demonstrate:

  • Two methods for adding new blank worksheets to the Excel workbook
  • Two Methods for inserting an exact copy of an existing – and formatted – worksheet
  • My favorite tip – Fill Across Worksheets – either the formatting only, the contents only or the default setting – both formatting and contents.

Links to Additional Tutorials in this Series

Watch Tutorial In High Definition

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

Additional Resources at My Online Shopping Website

I invite you to visit my new – secure – online shopping website – http://shop.thecompanyrocks.com – and preview the many tutorials and resources that I offer you.

Watch Tutorial Now

Link to this post!
Filed Under: Excel Training, Format Cells in Excel, Tips and Time Savers, Work with Multiple Worksheets, YouTube Videos by Danny Rocks Tagged: , , , , , , , ,
April 7, 2011 Leave a Comment

Dynamically Update a Cell with an Excel Worksheet Name

Formula to Capture Worksheet Name

Capture Tab Name in Cell

I am excited to share this Excel Tip with you! Several viewers have recently asked if there was a way to create a link in a cell to the name of a worksheet tab. Yes there is! And, in this lesson, I demonstrate how to do this.

Say, for example, that you want Cell C1 to contain the name of the current worksheet (Sheet1). With this formula, when you change the name of the worksheet to “January,” cell C1 is dynamically updated to show “January.”

Perfect for Excel Templates

If you use a template for your monthly, regional sales or financial reports, then this Excel formula is essential. You write the formula once – using Group edit – and each worksheet in your Excel workbook will be linked to a cell in your report. Change the name of the worksheet and your report name is automatically updated.

Functions Used in this Formula

  • CELL() – to gather information about the worksheet – in this case, the “Filename.”
  • MID() – to extract the characters in a text string – e,g, the “worksheet name.”
  • FIND() – to locate the starting point for the MID() function to extract the characters in the text.

As the final argument for the MID() Function, I use 31 characters because that is the maximum number of characters that you can use when naming an Excel worksheet.

Try this for yourself. Let me know how it works for you. Add your comments below.

Download Excel Workbook for this Lesson

Watch Video in High Definition

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

I invite you to watch or to subscribe to my video podcast on iTunes. Follow this link to learn more about my RSS Feed.

Create a Link to an Excel Worksheet Name in a Cell [ 6:06 ] Play Now | Play in Popup | Download (1667)
Link to this post!
Filed Under: Download Files from Video Lessons, Excel Formulas, Excel Functions, Excel Training, New in Excel 2007, Podcast Tips & Timesavers, Workbooks and Worksheets in Excel Tagged: , , , , , , ,
December 14, 2010 1 Comment

Working with Multiple Worksheets in Excel

The 50 Best Tips

The 50 Best

In this video lesson, I share my tips for maintaining a consistent look when working with multiple worksheets in Excel. I will demonstrate how to:

  • Group multiple worksheets for editing
  • Make a copy of an Excel worksheet
  • Use the Fill Across Worksheet tool – to update Contents or Formats or Both – for a Group of worksheets

In a professional setting, it is important to make a consistent presentation of your Excel worksheets. You want each worksheet in the group to use the same formatting styles, headers and formulas. The tips that I demonstrate will definitely save you time and help you to maintain a consistent look.

Click here to watch this video in High Definition, Full Screen mode on my DannyRocksExcels YouTube Channel.

Learn how to “Master Excel in Minutes – Not Months!”

Invitation to Visit My New Secure Online Shopping Site

I have just opened my new, secure online shopping website. I invite you to visit http://shop.thecompanyrocks.com to see the new products that I have introduced in the year 2011.

Working with Multiple Worksheets in Excel [ 7:36 ] Play Now | Play in Popup | Download (1114)
Link to this post!
Filed Under: Excel Functions, Excel Training, Podcast Tips & Timesavers, Work with Multiple Worksheets Tagged: , , , ,