Danny Rocks Tips and Timesavers » Podcast Feed
By Danny Rocks
To listen to an audio podcast, mouse over the title and click Play. Open iTunes to download and subscribe to podcasts.
Podcast Description
Video tips that help you to save time when using popular software programs. Also, videos that help you improve your communications skills.
| Name | Description | Released | Price | ||
|---|---|---|---|---|---|
| 1 | VideoSummarizing Employee Hours Worked and Vacation Time over 52 Weeks in Excel | Sum Across Worksheets This is the third episode in my series of Excel Tutorials where I share my best practices for creating an Excel Workbook that contains 52 weeks of Employee Time Cards. In this lesson, I focus on techniques to Summarize – or Consolidate – the hours worked or taken as vacation, by each employee, over a period of several weeks. Sum Across Worksheets This is a great tip to learn! In my experience, I find that very few experienced users of Excel know that you can do this! This will eliminate the hassle of linking to a cell in individual worksheets to create your formula. There is, after all, a limit to the number of characters that you are allowed in a formula! Consolidate Data By Position Since each worksheet in this workbook is identical in structure, Data Consolidation is simple. Provided, that is, that you remember to “Add” each worksheet range reference in the Dialog Box. In this example, I have “created a link” to each worksheet so that the data can be easily updated. Links to Other Tutorials in this Series Use a Template to Create a 52 Week Employee Time Card Workbook in Excel Tips for Creating a 52 Week Employee Time Card in Excel Shop for Additional Resources I invite you to visit my new, secure online shopping website – http://shop.thecompanyrocks.com – where you can 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 Share and Enjoy: | 12/16/11 | Free | View In iTunes |
| 2 | VideoHow to Use the New Natural Language Date Filters in Excel | Natural Language Date Filters Recently, one of my viewers asked me to go into greater detail in demonstrating how the new Natural Language Date Filters work in Excel. Natural Language Filters were introduced in Excel 2007 and they are a great tool to use! Now, instead of writing complex formulas as criteria in Advanced Filters, you can simply click, “Yesterday” to see all of the records from the previous day! Of course, you must actually have records in your data set for that date! The Natural Language Date Filters are related, by position, to TODAY(). The TODAY() Function is a “Volatile Function” that returns the value for the current date as found in your computer systems internal clock. The result of the TODAY() Function will change each day. And so, by definition, will the result for a “Yesterday” filter! Filter for Specific Date There are several methods that you can use to filter for a specific date or range of dates. One method that I demonstrate in this tutorial is the “Custom Date” dialog box. Watch Tutorial in High Definition Follow this link to watch this Excel Tutorial in High Definition on my YouTube Channel - DannyRocksExcels Learn About My New Extended Length Video Tutorials I have recently released a series of extended length (90 minutes) video tutorials. They are part of my “Master Excel in Minutes” Series. Each video focuses on one topic. My first video is focused on Excel Pivot Tables. I have created Videos for Excel 2010, Excel 2007 and for Excel 2003. You also have the choice of purchasing the video for immediate downloading or shipped to you on a DVD-ROM. Follow this link to go to the information page for my “Master Excel in Minutes” video tutorial series. Share and Enjoy: | 11/24/11 | Free | View In iTunes |
| 3 | VideoHow to Automatically Add a Time / Date Stamp Whenever You Add or Edit a Record in Excel | VBA Code to add Time Stamp In this video tutorial, I respond to one of the most frequent requests that I get from my viewers – “How do I automatically add a ‘Time / Date Stamp’ whenever I add or edit a record in my Excel Worksheet?” On the surface, this might appear to be a “simple” question to answer. However, I promise that you will be frustrated if you try to solve this problem by using the =NOW() Function or one or both of these Keyboard Shortcuts: Ctrl + ; (Semi-colon) to add a Date Stamp Ctrl + Shift + : (Colon) to add a Time Stamp Because … NOW() is a “Volatile Function” that automatically updates to display – in all records that refer to NOW() – the current date and time. And … because, it is difficuly to automate the combination of Keyboard Shortcuts to apply a Date / Time Stamp. So … what is the solution? Use VBA to Create a Simple “Event Handler” Macro Don’t get scared off! Don’t say, “I can’t write a VBA Macro!” I PROMISE you – an Intermediate Level User of Excel – that by investing 10 minutes to watch this tutorial – that you WILL be able to automate this process to add a Time / Date Stamp whenever you add or edit a record in Excel. I had you – the Intermediate / Early Advanced Level Excel user in mind when I created this video tutorial. I take you step-by-step through the process. I point out the potential “got’cha” steps. I am confident that I can help you to advance your skill set in Excel with this video tutorial! Watch Tutorial in High Definition I invite you to watch this Excel Tutorial in High Definition on my YouTube Channel - DannyRocksExcels Visit My Online Shopping Site I invite you to visit my new, secure online shopping site – http://shop.thecompanyrocks.com – to see all of the new products that I have created and offer for sale! Share and Enjoy: | 11/21/11 | Free | View In iTunes |
| 4 | VideoTips for Working with Array Formulas and Functions in Excel | ARRAY Formulas in Excel Array Formulas and Functions are very powerful tools to use in Excel. However, the concept of working with ARRAYs take a little bit of time and practice. In this video tutorial, I demonstrate how to: Create formulas that use ARRAYS as arguments Work with ARRAY FUNCTIONS – for example, the TRANSPOSE Function Key “Got’cha” steps to master – for example, selecting all cells to receive formula results before creating the ARRAY Formula. The importance of using Ctrl+Shift+Enter to complete the Array formula. What is an ARRAY? “An Array is a collection of Cells or Values that Excel treats as a single unit.” Why Use an ARRAY Formula? Automatic Level of Protection for Formula Cells – You cannot delete nor edit a single cell in an Array Formula Eliminate Intermediary Calculations – For example, you can find the Grand Total without having to create a field to calculate “Extended Price.” Worksheet, usually, calculates faster because you are using fewer formulas. Visit My NEW Online Shopping Website http://shop.thecompanyrocks.com is my new, secure online shopping website. I invite you to visit and preview my new products. Watch Tutorial in High Definition Follow this link to view this tutorial in High Definition on my YouTube Channel - DannyRocksExcels Share and Enjoy: | 11/13/11 | Free | View In iTunes |
| 5 | VideoMaster Excel Pivot Tables in 90 Minutes: Announcing My New Extended Length Video Tutorials! | Master Pivot Tables in Minutes I am proud to announce the publication of my new series of “Extended Length” video tutorials: “Master Excel Pivot Tables in 90 Minutes!” Now, you can can get an in-depth video tutorial, so that you can Master Excel Pivot Tables with 90 minutes of video instruction. You also receive the actual Excel Workbook file that I used while filming the video lessons as well as a printed step-by-step instructional manual. What Makes this Product Unique? Excel Versions for the Video Tutorials Available for Excel 2010, Excel 2007 or Excel 2003 Multiple Options for Delivery Order for Immediate Download Order a DVD-ROM to be shipped to you Resources to Assist Your Learning The Excel Workbook file that I used while filming each video lesson – Included with purchase! PDF Manual of Step-by-Step Instructions – That you can print out – Included with purchase! Take a Short Video Tour Follow this link to watch a short – 5 minutes – video tour of my new products on my YouTube Channel – DannyRocksExcels Visit My New Secure Online Shopping Website I invite you to visit http://shop.thecompanyrocks.com This is my new online shopping website. I have created secure shopping carts to ensure that your privacy is protected. I also offer a 100% Customer Satisfaction Policy. If, for any reason, you are not 100% satisfied with any of my products, I will refund your purchase with no questions asked! Danny Rocks The Company Rocks Share and Enjoy: | 10/26/11 | Free | View In iTunes |
| 6 | VideoHow to Use the Solver Add-in for Excel – Part 2 | Solver Parameters This is Part 2 of my series of video tutorials where I demonstrate how to use the SOLVER Add-in Tool in Excel to produce a specific result for a formula. Remember that SOLVER is an Add-in program that you must activate before you can use it. Watch Part 1 in this series to learn how to activate SOLVER. In this tutorial, I work with the same data set that I used in Part 1. This time, I want to achieve a specific amount of Gross Profit Sales Dollars. First, however, I must determine what is a reasonable amount to set as the Target for Solver to return. Use SUMPRODUCT Function in Excel I use the SUMPRODUCT Function to quickly determine my possible Revenue, Cost of Goods Sold and Gross Profit is I sell all of the Units that I have in stock. Now, I have a reasonable Target for SOLVER to reach. SUMPRODUCT is a very efficient Function that, unfortunately, very feel Excel users – in my experience – know about. Follow this link to my Index of Excel Video Lessons to find additional tutorials for SUMPRODUCT. Best Practice Tips for Solver Work with a copy of your worksheet. This is always a good idea, and it is most important when you are beginning to learn how to work with SOLVER. Write out your Goal for SOLVER. Do you want SOLVER to Maximize the Formula? For example, to Maximize your potential profit? Do you want SOLVER to Minimize the Formula? For example, to Minimize your costs? Do you want SOLVER to return a Specific Number? If so, what is that number? Determine your “Variable Cells.” SOLVER calls them the “By Changing Cells.” These are the cells that SOLVER will automatically adjust. The Variable Cells must “feed int” the formula in your Target Cell – This is the key to making SOLVER work! Write out your “Constraints.” These are the “real-world” conditions that you want SOLVER to operate under. This is the real power in SOLVER. Do not treat the SOLVER results as “the Gospel.” SOLVER is a powerful Business Intelligence Tool. You, however, know your own business or industry. Use SOLVER as a tool to help you to make informed business decisions. Watch Part 1 in SOLVER Series of Video Tutorials Here is the link to watch Part 1 of my series of Excel Tutorials for the Solver Add-in Tool. Watch Part 1 of this Series in High Definition on YouTube – My Channel is DannyRocksExcels Watch Tutorial in High Definition Follow this link to view this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels. Share and Enjoy: | 9/23/11 | Free | View In iTunes |
| 7 | VideoHow to Use the SOLVER Tool in Excel to Produce the Optimal Result for a Formula | SOLVER in Excel - Key Points I am responding to a “flurry” of requests to produce an Excel Video Tutorial that demonstrates how to use the SOLVER TOOL in Excel. Here are the key points to remember when you want to use the SOLVER Tool: Activate the SOLVER Tool SOLVER is an “Add-in” tool that needs to be “activated” before you can use it. In Excel 2007 / 2010, you do this via Excel Options – Addi-Ins – Excel Options. In Excel 2003, you do this via the Tools Menu – Add-ins – Solver. Prepare Data for Solver In order to use the SOLVER Tool, you must: Establish the “Target Cell.” This cell must contain a formula. This is the cell that you want to “optimize” by using SOLVER. Determine the “By Changing” Cells. These are the cells that SOLVER will change in order to deliver the optimized result in the Target Cell. The “By Changing” cells MUST “Feed Into” the Target Cell. Establish the CONSTRAINTS for SOLVER. This is the “key’ element that gives SOLVER its power. You can add as many constraints as needed. My recommendation is that you “write them out” before you start to use the SOLVER Tool. SOLVER is very powerful. However, you MUST “think it through” before you apply it. It will take lots of practice for you to understand how to properly set up your data to produce appropriate SOLVER results. Part 2 in this Series of Tutorials for Solver in Excel Here is the link to watch Part 2 in this Series of Tutorials about the SOLVER Add-in Tool in Excel Download Excel Workbook for this Lesson Follow this link to download the Excel Workbook that I used for this lesson. I store my uploaded Excel workbooks on my SkyDrive at www.office.live.com View this Tutorial in High Definition Click on this link to watch this Excel Tutorial in High definition / Full Screen Mode - on my YouTube Channel, DannyRocksExcels Share and Enjoy: | 9/21/11 | Free | View In iTunes |
| 8 | VideoHow to Prevent Duplicate Entries in Excel with Data Validation | Formula for Data Validation Over time, it is easy for duplicate entries to creep into an Excel Data Set. In this video tutorial, I show you, step-by-step, how to apply Data Validation to a range of cells so that anyone who attempts to enter a duplicate value will be prevented from doing so. Use a “Logical Formula” for Data Validation In this lesson, I show you how to “customize” the COUNTIF Function in the Data Validation Dialog Box so that it returns the value of TRUE. Logical formulas return either TRUE or FALSE. You MUST use a Logical Formula in Data Validation. What Else is Covered in this Tutorial? Use Paste Special – Validation to extend Data Validation to a wider range of cells Use Find – Go to Special – Validation to highlight all cells on worksheet that contain Data Validation Use Circle Invalid Entries from the Data Validation menu to automatically “draw a red circle” around existing cells that contain duplicate values How to quickly remove Data Validation from a Range of Cells Related Topic – Conditional Formatting in Excel Another way to highlight existing cells that contain duplicate values is to use Conditional Formatting. The improvements in Excel 2007 and Excel 2010 make this “a snap” because this is now a Menu Selection! In my next tutorial, I will show you how this is done in both Excel 2003 – with the COUNTIF Function – and from the new Menu Selections introduced in Excel 2007. Watch Tutorial in High Definition Follow this link to my YouTube channel - DannyRocksExcels – where you can watch this tutorial in High Definition mode. Share and Enjoy: | 9/8/11 | Free | View In iTunes |
| 9 | VideoKeyboard 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: | 9/6/11 | Free | View In iTunes |
| 10 | VideoExcel Keyboard Shortcuts to Fill a Series | Here is the latest installment in my series of video tutorials on Keyboard Shortcuts in Excel. In this lesson, I demonstrate how to Fill a Series with Values or a Formula Down or to the Right. The more interesting Keyboard Shortcuts that I demonstrate in this lesson are: Ctrl + ‘ (apostrophe) – to copy the value from the cell directly above; to copy the formula from the cell directly above while retaining exact cell references. Ctrl + Shift + ” (double quote) – to copy the value from the cell directly above; to copy the “result” of the formula from the cell directly above. Download My Free “Chart of Popular Keyboard Shortcuts in Excel” Follow this link to download all of my Popular Keyboard Shortcut Charts from my Exclusive Membership Content page. You can also download this – for FREE – at my Online Shopping Website – here is the link: http://shop.thecompanyrocks.com/chart-of-popular-keyboard-shortcuts-for-excel/ Watch Tutorial in High Definition Click here to go to my YouTube Channel – DannyRocksExcels – where you can watch this tutorial in High Definition mode. Share and Enjoy: | 8/24/11 | Free | View In iTunes |
| 11 | VideoHow 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: | 8/22/11 | Free | View In iTunes |
| 12 | VideoHow to Use Keyboard Shortcuts in Excel to Control Basic File Operations | Excel 2007 File Operations If you have recently upgraded to either Excel 2007 or Excel 2010, you REALLY NEED to master the Keyboard Shortcuts that I present in this lesson! Many Excel veterans who upgrade, tell me that they initially felt “lost” with the new user interface. Some of my clients called me in a panic because they didn’t know where to look to Print their work or to open a document. If you know how to use the Keyboard Shortcuts that I present in this lesson, you will never be at a loss. As an added bonus, these Keyboard Shortcuts work with ALL Microsoft Office programs and also for most Windows-based programs. The “N-O-W-S” Keyboard Shortcuts I use this acronym – NOWS – to remember the keyboard shortcuts to Open, Close and Save Documents in MS Office programs. Use Ctrl + W to Close the active worksheet. In this video tutorial, I also show you when NOT to use Keyboard Shortcuts! There are many times when you need to access the full file menu selection. Keyboard Shortcut for Print Preview Use Ctrl + F2 to open up the Print Preview pane. Use the “Esc” key to Exit Print Preview. Download My Chart of Popular Keyboard Shortcuts as a PDF Follow this link to go to the web page where I store my Keyboard Shortcut Charts and other download-able content. These Keyboard Shortcuts work for all versions of Excel. So far, I have found only one Keyboard Shortcut that is different when used in Excel 2007 / 2010 than in Excel 2003. More Video Tutorials for Keyboard Shortcuts This is the first in a series of video tutorials that I have planned. Each video will focus on the Keyboard Shortcuts you can use to perform a specific task – e.g. Formatting Cells, Inserting and Deleting, etc. My plan is to post one new video in this series per week. Watch Video Tutorial in High Definition Mode Follow this link to view this video tutorial in High Definition on my YouTube Channel - DannyRocksExcels Share and Enjoy: | 8/12/11 | Free | View In iTunes |
| 13 | VideoUse the Show Values As Formulas in Excel Pivot Tables | Show Values As in Pivot Table I participate in a number of Group Discussion Boards on LinkedIn. In this video tutorial, I address one question that was recently asked: “Is it possible to show a Percentage of Running Total formula in a Pivot Table?” The answer is, “Yes, if you are using Excel 2010. If you are using earlier versions of Excel, you can use a work-around outside the Pivot Table.” New in Excel 2010 A great new feature introduced in Excel 2010 is the drop-down menu for the “Show Values As’ in a Pivot Table Calculation. In earlier versions of Excel, the Show Values As option was not so obvious. And, I found that many of my clients did not know how to use it – or could not locate the drop-down menu. Work-Around for Show % of Running Total While the “Running Total in…” calculation has been available for quite some time, the “% of Running Total in …” formula is brand new in Excel 2010. The work-around that I demonstrate is to first, create the Show As “Running Total in …” formula. Then change the Font and background of these cells to “White.” Next, write a standard formula “outside of the Pivot Table” that references these cells. Be sure to turn off the “GetPivotData” Formula when you write this formula. Download Extended Length Pivot Table Video Tutorials Click on this link to learn about my new “extended length” video tutorials for Pivot Tables. They are availabe for Excel 2003, Excel 2007 and Excel 2010. Watch Video in High Definition on YouTube Follow this link to watch this video in High Definition on my YouTube Channel - DannyRocksExcels Master Excel Pivot Tables in 90 Minutes Follow this link to learn about my new “Extended Length” – 90 Minutes – Video Tutorials Share and Enjoy: | 7/31/11 | Free | View In iTunes |
| 14 | VideoThree and a Half Ways to Average Excel Data Using Criteria | AverageIf Function in Excel It is easy to use Excel’s AVERAGE Function to get the average for an entire range of data. But, what if you are asked to produce a report that shows the average for select groups of records – for example, average test results for the female students only; or for the male students only? What approach do you take? What Functions do you use to produce this report? Three and a Half Ways to Produce this Report SUBTOTALS - Easy to use. Remember to sort your data first using the field that you want to subtotal. AVERAGEIF Function – Very easy to use. Only works in Excel 2007 and Excel 2010. DAVERAGE Function – Will work in any version of Excel. Easy to add or change the Criteria. PIVOT TABLE – Very easy to create. No need to write a formula. Create report with @ 6 mouse clicks! My Preferred Approach – Create a Pivot Table Report While Pivot Tables are the most powerful tool in Excel, they are also one of the easiest tools to use in Excel! Why? Because you can create a Pivot Table Report without writing a single formula! You can create this Pivot Table Report with about six clicks of the mouse. You cannot possibly harm your underlying data when you create Pivot Tables! Download a One Hour- Plus / In-depth Video Tutorial on Pivot Tables – for $9.95 I have created a series of Excel Video Tutorials that you can download for $9.95 US Dollars each. Here is a link to my informational page that gives you the details. Are you Using Excel 2003? – I created a video recording for Pivot Tables using Excel 2003 that you can download for $9.95! Are you Using Excel 2007? – I created a video recording for Pivot Tables using Excel 2007 that you can download for $9.95! Are you Using Excel 2010? – I created a video recording for Pivot Tables using Excel 2010 that you can download for $9.95! As with all of my products, I guarantee that you will be 100% Satisfied or I will refund your purchase price – no questions asked! My Extended length Excel Training Video Recordings are hosted at: http://thecompanyrocks.webex.com – I use the secure shopping cart at WebEx by Cisco Systems, so you can be sure that your personal information is safe and secure. Watch Video in High Definition on YouTube Follow this link to watch this video tutorial in High Definition mode on my YouTube Channel - DannyRocks Excels Get My DVD-ROM, “The 50 Best Tips for Excel 2007″ for only $29.97 Share and Enjoy: | 7/9/11 | Free | View In iTunes |
| 15 | VideoUse Excel’s AutoFill Tools and Formulas to Increment Time Intervals | In my experience, many Excel users get “frustrated” when trying to enter and calculate Time Intervals. For example, setting up a schedule for meetings that begin and end in 45 minute intervals. After you watch this video tutorial, you will learn how easy it is to set up this type of schedule! The Problem with Time in Excel When it comes to entering Time Values, Excel will only accept a short list of Time Formats. For example, 6:00 AM is a valid Time Entry in Excel. However, Excel will treat 6:00 A.M. as a “text entry!” 6:00 AM is stored as the decimal value 0.25 in Excel. Decimals are numbers that Excel can calculate and increment. In this video, I demonstrate a shortcut that ensures that you always enter a Time Value as a Number. How to Increment in Two-Hour Intervals As I demonstrate in this video, use this formula to increment a series of two-hour time intervals: =starting_cell + 2*(1/24) In this case the (1/24) portion of the formula indicates a “one-hour segment.” 1 hour out of a 24 hour day. The +2 part of the formula indicates that you want to increment by 2 – of what? The answer is +2*(1/24) – by 2 hours. To indicate a 15 minute increment use this formula: =starting_cell + 15 * (1/24/60) – That is, divide each one hour segment (1/24) by 60 minutes (1/24/60) and multiply this by the number of minutes that you wish for your schedule. Watch Part 1 of this Series – Increment Dates in Excel Follow this link to watch Part 1 of this two-part series – “Use Excel’s AutoFill Tolls and Excel Functions and Formulas to Increment Date Intervals.” View this Tutorial in High Defintion on YouTube Click on this link to watch the video lesson 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: | 7/8/11 | Free | View In iTunes |
| 16 | VideoUse Excel’s AutoFill Options and Formulas to Increment a Series of Dates | Excel AutoFill Options I begin this Excel Video tutorial by first, demonstrating how to use Excel’s AutoFill Options to increment a series of dates. Some of these AutoFill Options are: Fill Weekdays Only (Monday through Friday) Copy Formatting Only Increment by Adding one month to the previous cell Increment by Adding one year to the previous cell Here’s a Tip: If you use your Right-mouse button to AutoFill a series, a menu of options will pop up automatically when you release the mouse. Try it! Excel Functions and Formulas to Increment a Series of Dates Two of my favorite Excel Date Functions are part of the “Analysis ToolPak” Add-in: The EDATE() Function – returns a “serial number” for a Month that is X number of months away from the starting date. The EOMONTH() Function – is similar to EDATE. However, it always returns the last Calendar Day of the Month that is X number of months away from the starting date. In Excel 2007 and Excel 2010, the Analysis ToolPak Add-in is activated by default. In this video, I demonstrate how to activate it if you are using Excel 2003 or older. Other Functions Used in this Video Tutorial The DATE() Function. Remember that if you use this function to increment by one-year intervals that you need to “nest” the Year(), Month() and Day() functions in the arguments. This is a “tricky” function to use. The WEEKDAY() Function. In the video, I nest this function inside an IF() Function in order to get a series of dates that include Monday through Friday only! Related Video Tutorial My next video in this series demonstrates how to use Formulas to increment a series of times by “minutes” or by “hours.” Watch This Video in High Definition Follow this link to view 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: | 7/7/11 | Free | View In iTunes |
| 17 | VideoWorking with Custom Lists in All Versions of 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. | 6/29/11 | Free | View In iTunes |
| 18 | VideoHow a Single Blank Cell Affects an Excel Pivot Table Report | As you will see in this Excel Video Tutorial, having a single blank cell in a data set will greatly affect how Excel Functions and Pivot Tables are designed and calculated. Single Blank Cell in Data Imagine, how a single blank cell, in a field of 848 records, can change the way your Pivot Table views the data type (text vs. the expected numeric data type) and subtotal function (Count vs. the expected SUM function). Seeing is believing! Preventing a Blank Cell from “Creeping into” your data set Use the Keyboard Shortcut Ctrl + Down Directional Arrow to go to the last cell that contains data in a column. This is a great way to isolate “stray” blank cells that will have a great impact on your Pivot Tables and other calculations in Excel. Also, in Excel 2007 and Excel 2010, look for where a “numeric” field is placed by default. A single blank cell will cause an (intended) numeric field to be placed in the Row (text) labels area. Even dragging this field into the VALUES Drop zone does not solve the problem because the COUNT Function will be used to Subtotal this field. This is a “disaster waiting to happen!” Introducing My Extended Length Excel Video Recordings This is my opportunity to introduce you to my new, extended-length (one-hour +) Excel Video Tutorials that you can download for only US $9.95! Here is the link that gives you more information about this exciting new service: http://www.thecompanyrocks.com/pivot-table-tutorial-videos-to-download-practice-files-instructional-manual-included/ I offer you multiple options: If you are using Excel 2003, you can download the video recording for that version. If you are using Excel 2007, you can download the video recording for that version. If you are using Excel 2010, you can download the video recording for that version. You can quickly download the PDF of Step-by step instructions for each version. You can download the Excel Workbooks that I use in each recording. Let me know what you think! Let me know which topics you would like me to record for you to download. Send me an email with your comments and suggestions: danny@thecompanyrocks.com Watch This Video in High Definition Follow this link to view this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels Share and Enjoy: | 6/27/11 | Free | View In iTunes |
| 19 | VideoCreate a Custom Excel Function to Replace Formulas with Multiple IF Functions | Do you get tied up in a knot trying to write - let alone explain - a formula that has one IF Function nested inside another, and another...? I know that I do! And, what happens when your sales manager wants to revise the tiers for the quantity discounts? How many hours will this take you to review your logic and make all of those multiple changes? Usually a lot of time, a lot of frustration and possibly some resentment thrown in as well! | 6/25/11 | Free | View In iTunes |
| 20 | VideoHow to Maintain Accurate Subtotals When Your Data Set Expands in Size | Subtotal in Excel 2010 Table This is Part 2 of my series of video tutorials demonstrating how to use the SUBTOTAL Function in Excel. In Part 1, I showed you the value of using the Subtotal Function to summarize the results of applying a Data Filter to a range of cells. In this part, I show you how to use an Excel 2007 or Excel 2010 Table to ensure that your Subtotal Formulas are automatically updated when you append records or add additional fields to your original data set. I strongly recommend basing Filtered Lists and Pivot Tables on an Excel Table (in Excel 2007 or 2010) or an Excel List in Excel 2003. This way, any formulas, filters and references that you make will be automatically updated when you append additional records or otherwise change the structure of your data set. Function Numbers 101 through 111 Notice that when you “toggle on” the Total Row for a Table or List that Excel uses this formula = SUBTOTAL(109, Table1, [Sales]). Function 109 will use the SUM Function(109) to total the values in the “Sales” field ([Sales]) of a Table named “Table1.” These Function Numbers + 100 were introduced in Excel 2003 and the are automatically applied whenever you are using a Total Row in an Excel Table. I think that you will learn some cool tricks in this lesson. Let me know what you think! Watch This Video in High Definition Click on this link to watch this video tutorial in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels. Invitation to Visit My New Online Shopping Site I invite you to visit my new, secure online shopping website – http://shop.thecompanyrocks.com Once there, you can get my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007″ Share and Enjoy: | 6/3/11 | Free | View In iTunes |
| 21 | VideoNew Keyboard Shortcuts for Paste Special in Excel 2010 | Content Key on Keyboard In Excel 2010, you can now use special keyboard shortcuts to control your Paste Special Options – e.g. Paste Values Only, Transpose, Paste Link, Paste Formatting, etc.). There is a new technique to learn, however, before you can become proficient with these keyboard shortcuts in Excel 2010. Let me demonstrate. Live Preview for Paste Special in Excel 2010 In my previous video tutorial, I demonstrated how to take advantage of this great new feature – “Live Preview for Pasting” – in Excel 2010. Click here to watch that video. Two Ways to Use Keyboard Shortcuts to Paste Special in Excel 2010 Press and Release the Ctrl Key, then press the “letter key” for the shortcut. Press the “Context Key” on your keyboard (to the right of the Space-bar; between the Alt & Ctrl keys) and then press the “letter key” for the shortcut. Each of these techniques is easier to explain through a video demonstration than to write out the instructions. So, I encourage you to watch my video if you want to learn these new techniques for Excel 2010! Watch Video in High Definition on YouTube Follow this link to watch this video tutorial in High Definition on my YouTube Channel – DannyRocksExcels. Share and Enjoy: | 5/30/11 | Free | View In iTunes |
| 22 | VideoIntroducing Live Preview for Paste Special in Excel 2010 | Gallery of Paste Special Options I have always loved the many “Paste Special Options” available in Excel. Now, in Excel 2010, there is a great new tool available - “Live Preview of a Gallery of Paste Special Options” – to help you to discover or to choose the best way to paste items copied to the clipboard. From either the Paste Menu on the Home Tab of the Excel 2010 Ribbon or from the right-click shortcut menu, you can hover your mouse over one of the Gallery Icons to see a “Live Preview” of your Paste Special Option Selection. When I demonstrate this feature to my clients, they are amazed to discover the many options that are available! Of course, some of these same clients are also “overwhelmed” by all of the choices on the menu! That is why I created this video – to help you to discover and to select the best Paste Special Option for your needs. Paste Between MS Office Programs Later in this video tutorial, I also show you how to use the Microsoft Office Clipboard and the Gallery of Paste Special Options to copy a data set from Excel and Paste it as a Picture in a Word document. With the Office Clipboard, you can cut, copy and paste up to 24 items between Excel, PowerPoint and Word. However, you must first open up the Office Clipboard for this tool to be available to use. Additional Paste Special Features in Excel 2010 I have decided to produce an addition video tutorial to demonstrate how to use some new Keyboard Shortcuts in Excel 2010 to take advantage of the Paste Special Options. Watch Video in High Definition Mode Click here to watch this video in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels Share and Enjoy: | 5/28/11 | Free | View In iTunes |
| 23 | VideoHow to Apply Conditional Formatting to a Pivot Table in Excel | A Pivot Table is a great way for you to summarize and present the information contained in your data set. When you apply conditional formatting to your Pivot Table, you can "visually" spot important trends or anomolies in your data. | 5/13/11 | Free | View In iTunes |
| 24 | VideoHow to Create an Interactive Pivot Chart in Excel | While I have created many Excel Video Tutorials demonstrating the power of interactive Pivot Tables during the past four years, I have NEVER created a video lesson focused on creating interactive Pivot Charts in Excel – until now! Pivot Charts in Excel 2007 In this lesson, I show you how to create a Pivot Chart in Excel 2007. Starting with Excel 2007, there are several major changes: By default, Pivot Charts are embedded on the same worksheet as the Pivot Table Report that is linked to the chart. A new, PivotTable Chart Filter Dialog box is available – this works “in tandem” with the filters available in the PivotTable Field List” Dialog box. It is very easy to “Move” a Pivot Chart to its own worksheet tab – or vice versa – to move the Pivot Chart to an existing worksheet. Pivot Charts and Pivot Table Reports are Connected A Pivot Chart MUST be connected to a Pivot Table Report. Any changes that you make to the chart are reflected in the report and vice versa. If you filter the Pivot Table Report, the filter is automatically applied to the Pivot Chart. This is an important concept to understand. This is the reason why it is so easy to make BOTH Pivot Table Reports and Pivot Charts “interactive!” Explore My Index of Free Excel Video Lessons I was surprised to discover that I had not yet created a video tutorial for Excel Pivot Charts when I looked at my list of “free” Excel Video Lessons. This became the impetus for creating this lesson. I invite you to explore all of my free Excel Video Tutorials. Click on this link to go to my Index of Excel Video Lessons. I have organized the free video lessons by topic. Simply click on the title of any lesson and you will arrive at the posting and the video for that lesson. Download Excel File for This Lesson Click this link to access the Excel file that I used in this lesson. It is stored on my Sky Drive at www.office.live.com View this Lesson in High Definition I invite you to view this Excel Video Tutorial in High Definition, Full-screen Mode on my YouTube Channel – DannyRocksExcel I always welcome your feedback. Let me know what you think about this – or any other – video lesson. Add your comments below! Share and Enjoy: | 5/11/11 | Free | View In iTunes |
| 25 | VideoMaking Effective Use of “White Space” on PowerPoint Slides | When you hear the term “White Space,” think “Breathing Room.” As a nation, we tend to scan a document before making the decision to go back and read it – or delete it!. When you are able to understand the strategic use of creating “white space” on your PowerPoint Slides, you take the first step towards getting your audience to “pay attention” to what you have to say – to what you have to offer to them! Use Paragraph Spacing to Make Bullet Points More Effective In this video tutorial, I demonstrate how to effectively use “paragraph spacing” to make your bullet points more effective during your presentation – by effectively using “white space” between each bullet point. Do NOT hit the “Enter” key repeatedly to add spacing between bullet points! Rather, learn how to use the “Paragraph Spacing” dialog box to control the amount of space before and / or after each paragraph. Each time that you press the ENTER key you create a new paragraph! So, effectively, each Bullet Point is a single paragraph! Learn how to use this fact strategiclally to create the message that you want to convey to your audience. Watch – as I create “Before” and “After” slides to illustrate how creating more “white space” between your bullet points makes each point on the slide more effective. One great Keyboard Shortcut to use as you fine-tune your paragraph spacing is the F4 key which repeats your last action. This is a powerful time-saver! SmartArt Diagrams Watch – as I show you how to convert a series of bullet points into a SmartArt Diagram to more effectively communicate your message to your audience. SmartArt Diagrams offer a welcome relief to an endles – or mindless – parade of bullet point ridden slides. You can quickly convert a series of bullet points into a Smart Art Diagram as you will see on this video. View this Video Tutorial In High Definition Mode Click on this Link to view my video tutorial in High Definition on my YouTube Channel - DannyRocksPowerpoint Share and Enjoy: | 5/5/11 | Free | View In iTunes |
| Total: 25 Episodes |
Customer Reviews
Concise training
These are very specific and concise training modules on individual aspects of Excel and Powerpoint. (Also a few on meeting management and social media.) No nonsense, down-to-business, accurate, and professional. If one of these matches the skill you want to quickly acquire, you should like these.
Viewers also subscribed to
- A Way with Words
- Martha Barnette and Grant Barrett, produced by Stefanie Levine
- View In iTunes
