Keyboard Shortcuts In Excel | The Company Rocks

Danny May 9, 2011 2 Comments

How to Convert a Pivot Table to a Standard List

Watch this video tutorial in High Definition on YouTube

A viewer aksed for my help. She has a boss and a few customers who:

Do not know how to create or use Pivot Tables
Have no desire to learn how to create and use Pivot Tables

Convert Pivot Table to List

Nevertheless, in this example, she is being asked to create a summary report – by Year and by Quarter – from @ 5,000 daily transaction records. The fastest way to create this report is with a Pivot Table. Now, to meet the demands of her boss and clients, she needs to convert this Pivot Table into a standard list that can be Filtered and Subtotaled.

Summarize by Year and Quarter

To create this Pivot Table report:

Add the “Date” field to the grid and then select a single date.
Group the Date field by Month, Quarter and Year.
Add and arrange the Customer and Invoice fields to the Pivot Table Grid.

Convert Pivot Table to Standard List

Remove both the Subtotals and Grand Totals from the Pivot Table Report.
In Excel 2007 & 2010, ensure that you are using either the Outline or Tabular report layouts – NOT the new “default” Compact layout.
Also in Excel 2007 & 2010, remove the Field Headers and the + / – Symbols.
Now, select the entire Pivot talbe (use Ctrl + A) and copy it to the clipboard.
Use Paste Special Values to paste the Pivot Table Results.

Filling the Blank Cells in the List

As you can see, whenever you have “nested rows” in a Pivot Table you get a nice, clean report. However, in order to Filter and Subtotal this new list, you will need to “fill in the blank cells.”

Select all of the values and all of the blanks in Column A.
Use Ctrl + G (or F5) to open the Go To Dialog Box and choose Special.
From the Go To Special choices choose “Blanks.”
With all of the Blank Cells selected use this formula: = ↑
Do NOT press Enter. Rather, press Ctrl + Enter and all of the Blank Cells are now filled in with the Customer Names!
Finally use Copy, Paste Special, Values to complete this process.

Watch This Video on My YouTube Channel – DannyRocksExcels

Since this video tutorial is a little longer than usual, I have decided to post it as a video – in High Definition – on my YouTube Channel. Click here to view it or copy and paste this link into your browser:

Link to YouTube Video: http://youtu.be/IwReVDJ7BAI?hd=1

Download This Excel Workbook

Click here to access the Excel workbook that I used in this lesson.

Filed Under: Excel Formulas, Excel Training, Pivot Tables, YouTube Videos by Danny Rocks Tagged: Excel Pivot Tables, Excel Video Training, Filter a List, FIlter a List in Excel, Formula to Fill in Blank Cells, Group Dates in Pivot Tables, Keyboard Shortcuts in Excel, New in Excel 2007, Paste Special Values, Video lessons for Excel

Danny February 7, 2011 Leave a Comment

Working With Array Formulas in Excel

There are at least two benefits to using an Array Formula in Excel:

They will speed up creating your formulas by, in many cases, eliminating intermediate formulas.
They will add a level of protection to your formulas because you cannot edit the individual cells in an Array Formula. You must edit “all” of the cells.

: Array Formulas

In this lesson, I first demonstrate how to select and use arrays with the SUMPRODUCT() Function in Excel. SUMPRODUCT is a very handy function to use when you want to first multiply (PRODUCT) the cells in two or more arrays and then total (SUM) the results.

Next, I demonstrate two ways to create Array Formulas. Notice that in the second example that it is important that you remember to select ALL of the cells that will contain the results of the Array Formula. It is vital that all arrays are of Equal Size (Same number of cells in the same dimension – horizontal and vertical).

Entering Array Formulas

Use the keyboard combination of Ctrl + Shift + Enter when entering an Array Formula. Do not press the Enter key alone – this will result in an Error for your formula! Some people refer to Array Formulas as CSE Formulas to remember the Ctrl + Shift + Enter combination. Notice, in the Formula Bar, that Excel automatically adds { braces } around the Array Formula. Do not enter these manually.

Editing Array Formulas

You gain a level of protection for your formulas when you use Array Formulas because you cannot select a single formula cell to edit it. You must select all of the cells that contain the Array Formula before you can edit it – or delete it. If you don’t select all of the cells first, you receive an Error message in a pop-up window. Remember to press the “CSE keyboard combination” when you complete your edit of the Array Formula.

Learning to use Array Formulas is an essential skill to acquire if you want to master Excel. I guarantee that you will be able to write more accurate formulas when you use arrays.

Watch this Video on YouTube

Click this link to watch this Excel Video in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels

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

Working with Array Formulas in Excel [ 7:45 ] Play Now | Play in Popup | Download (1004)

Filed Under: Excel Formulas, Excel Functions, Excel Training, Podcast Tips & Timesavers Tagged: Array Formulas in Excel, CSE Formulas in Excel, Excel Video Training, Formula Errors, Keyboard Shortcuts in Excel, SUMPRODUCT Funtion, Video lessons for Excel

Danny December 22, 2010 Leave a Comment

I Picked Up a New Tip Today – Filtering Your Excel Data

Yesterday, I posted an Excel Video Lesson on how to Filter your data. I focused on taking advantage of the Date Filters, Text Filters, and Number Filters introduced in Excel 2007. Today, in my email inbox, I received a great tip from Chandoo – whose tag line is “Become Awesome in Excel.”

Chandoo’s Tip – which I did not know – is to Right Mouse Click on any cell in your Excel Data Set and choose Filter – Filter by Selected Cell’s Value. As Chandoo points out, this only works in Excel 2007 and higher versions. I already use this tip in Microsoft Acces. In fact it is one of the Video Tips that I share on my DVD-ROM, “The 50 Best Tips for Access 2007.” So why didn’t I know that this would work the same way in Excel 2007? Because, I needed someone to point this out to me. Thank you Chandoo!

For me, the take-away point is to continue to explore other resources to learn from other expert users. Each – e.g. Mr. Excel – Bill Jelen; Mr. Spreadsheet – John Walkenbach, etc.) – has their own perspective on Excel. Each presents a topic in a slightly different manner. Each day is an opportunity to expand my knowledge base.

I also picked up another tip from Chandoo’s post – use the Ctrl + Shift + L Keyboard Shortcut to automatically add the Filter Drop Down Buttons to your Field Header Labels. I will use this tip on a regular basis. Now, I do not have to pick up the mouse to go to the Data Tab on the Ribbon and select Filter.

If you are using Excel 2007 and higher, note that you can also use this tip (rightmouse click in a cell) to filter by the selected cell’s Icon, Font Color and Cell Background Color. Data Visualization is one of the really great improvements introduced in Excel 2007. Click here to go to my video lesson on Data Visualization.

: Chandoo’s Tip

I recommend that you check out Chandoo’s Excel Blog by clicking on this link. You can click on the RSS Button – or go to http://feeds2.feedburner.com/PointyHairedDilbert directly to start receiving Chandoo’s Excel tips delivered automatically to your e-mail Inbox.

And, by the way, you can receive my new posts automatically by going to http://feeds.feedburner.com/DannyRocks which is my RSS feed on Feedburner.

I have now added Chandoo’s site to my Blogroll – so just click on the link whenever you visit The Company Rocks website.

Let me know if you have a special tip or a favorite website or blog that you want to share with our readers. Add your comment below.

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

Filed Under: Data Filters and Queries, Excel Data Tables and Lists, Excel Training Tagged: Blogging, Data Visualization, Excel Video Training, Filter a List, FIlter a List in Excel, Keyboard Shortcuts in Excel, Right Mouse Click Menu, Tips and Time Savers

Danny December 9, 2010 2 Comments

How to Work with Dates in Excel

The majority of the questions that I receive from viewers center on how to use and format DATES & TIMES in Excel. So, I have decided to create a series of lessons to show you how to work with dates and times in Excel. This is part one in a continuing series of free Excel Video Training Lessons.

Excel Tips

Learn How to Work with Dates

Use a Keyboard Shortcut to enter the current date in a cell – to “time stamp” the date.
Use the TODAY() Function to enter a “dynamic” date function in a cell – so that your date always reflects the current date.
Distinguish between how Excel “stores” a date – as a serial number – and how Excel formats a date – how you choose to format a date.
Tell if a date was entered correctly – as a number – or incorrectly – as “text” by seeing how it is aligned in the cell (right or left).
See why a fraction could be recognized as a date – and how to correct this “got’cha” step!
How to create a series of dates in your spreadsheet.

In my experience, the majority of “Excel problems” are based around incorrect date and time entries. So, whether you are new to Excel or a veteran who gets frustrated with Time and Date calculations, this lesson is for you.

If nothing else, you will pick up some great Keyboard Shortcuts in this lesson!

Follow this link to watch this video in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels

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

How to Work with Dates in Excel [ 8:55 ] Play Now | Play in Popup | Download (796)

Filed Under: Excel Basics, Excel Date and Time Functions, Excel Training, Podcast Tips & Timesavers Tagged: Date Calculations in Excel, Enter Dates in Excel, Excel Keyboard Shortcuts, Excel Video Training, Format Dates in Excel, Keyboard Shortcuts in Excel, Video lessons for Excel

Danny December 30, 2009 Leave a Comment

Announcing the Online Store for The Company Rocks

Danny's DVDs "The 50 Best Tips" Series Available in Online Store Today, I launched my new online bookstore for The Company Rocks. I am offering my Instructional DVDs for sale individually or as a set at attractive prices. Follow this link to enter the store to learn more about the DVDs in “The 50 Best Tips for …” Series.

“The 50 Best Tips for Excel 2007” contains over 5 1/2 hours of training. Each video lesson lasts @ 6 – 7 minutes. Each lesson focuses on one productivity tip. So you can get an answer quickly when you need it. I follow a step-by-step approach to teach you how to be more productive when you use Excel 2007. You can see how to enter and edit the formulas because I use an innovative “pan-in / pan-out” camera technique to focus your attention.

And, as a bonus: The actual spreadsheets that I use for each lesson are included on the DVD!

“The 50 Best Tips for PowerPoint 2007” packs over seven hours of training onto two DVDs! Unlike many instructional packages the teach you PowerPoint from a technical angle, I teach you how to incorporate PowerPoint into your total presentation. I am a professional speaker and trainer. I am not a “techie.” You will learn the technical side of PowerPoint 2007 – but only as it helps you to be more effective in presenting your ideas or products. I think that you will enjoy this difference!

The focus of each DVD is on the Intermediate to Advanced user of Excel or PowerPoint. The focus is on helping you to save time and take advantage of the great presentation elements in both programs. There are 10 videos each at the Beginning Level and at the Advanced Level. There are 30 videos for the Intermediate Level user.

Please visit my new online bookstore to learn more about each DVD. I welcome your feedback!

Thank you for your kind words of support as I have built my business these past three years. I wish you a very happy new year in 2010!

Danny Rocks

danny@thecompanyrocks.com

Filed Under: Business Communications, Online Store for The Company Rocks, PowerPoint Video Training, Video Training Courses Tagged: "What-if" Analysis in Excel, Data Validation, Excel Keyboard Shortcuts, Excel Pivot Tables, Excel Video Training, Group Dates in Pivot Tables, Keyboard Shortcuts in Excel, PowerPoint Presentations, The 50 Best Tips for ... Series, Video lessons for Excel

Danny October 25, 2008 Leave a Comment

Use the =SUMIF() Function to Tally YTD Sales

Many of my clients keep a “running total” of sales. They put “Dates” in Column “A” and enter each day’s sales in Column “B.” Next is Column “C” which contains the formula =SUM(B$:B250). That is fine – it works.

However, if you use the =SUMIF() function to total Year-to-Date (YTD) Sales you gain flexibility, save space and save time – because you do not have to scroll down several screens to see your YTD Sales total!

In this video, I show you how to use “Named Ranges” in a =SUMIF() Formula to keep track of your cumulative sales total.

Here are the steps to follow in this lesson:

Traditionally, you use a formula like =SUM(B$3:B250) to give you a “running total” for YTD Sales.
The=SUMIF() Function returns the SUM of vales that meet a “Single Criterion” (the IF portion) .
The 1st Argument is – What is the Range that you want your criteria to evaluate? Consider “Naming” this Range – it makes it easier to write your formula and explain your formula.
The 2nd Argument is – What is your criteria? Be sure to use this syntax – “Comparison Operator”
In the video example we also need to include the “&” to join the cell “D2.” Cell “D2,” in this example, contains the Function =TODAY()
The 3rd Argument is – What Range do you want to SUM to return the values that meet your Criteria?
The =DATE() Function returns the serial number for a specific date. It requires 3 Arguments. =Date, Year, Month, Day)
You will save time and gain productivity when you “Name Cells and Ranges” and then you them in your formulas.
Use the F3 shortcut key to “Paste Names” into your formulas.

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Find the Excel Video Lesson that you want – Index to all Excel Topics

Filed Under: Excel Date and Time Functions, Excel Functions, Excel Training, SUMIF and SUMIFS Functions Tagged: DATE() Function, Keyboard Shortcuts in Excel, Named Cell in a formula, SUMIF Function, TODAY() Function in Excel, Track cumulative sales in Excel

Danny October 9, 2008 4 Comments

Create an Excel Chart With One Keystroke!

It is easy to create a chart in MS Excel 2003. So easy, in fact, that you can create your chart with one keystroke.

In this video, I reveal that keystroke. I also demonstrate how easy it is to change the chart type and the location of your chart.

Here are the steps to follow in this Excel Video Lesson:

Select one cell in your data set and press the F11 key.
Excel, by default, creates a Column Chart in a new worksheet.
Using the Chart tool bar, you can change the chart type.
Select the data range(s) you want to chart before you press the F11 key.
Consider what “picture” you want to share before you create your chart.
Use the Chart Wizard – Step 4 – to move the location of the cart (from its own worksheet to an embedded object in your worksheet – or vice versa.)

Find the Excel Video Lesson that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

How to Solve Four Frustrations When Calculating Time in Excel

Have you ever been frustrated when you:

Entered a “Time Value” in a cell only to find that Excel did not recognize it as a “time value?”
Wrote a formula to calculate the elapsed time between “start and finish” only to find that Excel returned a “date” (e.g. 1/15/2008) and not a number?
Wrote a formula to calculate the hours worked for your “Graveyard Shift” (those whose work hours cross Midnight) only to get a cell full of ########?
Used the AutoSum function to calculate the total hours worked in a week only have Excel return a “silly” total like 1.7915?

Well, join the club! Most of my clients have experienced these four frustrations when they try to calculate “time values” in Excel. In this short video lesson, I will demonstrate how to solve each of these four frustrations.

I’d like to receive your feedback on this video lesson. Have you ever encountered this frustation? Did my explanation help you? What additional topics would you like me to cover?

You can email me – danny@thecompanyrocks.com

Here are the steps to follow in this video lesson:

Excel stores Time values as decimal numbers. Midnight is 0.00; 6:00 AM is 0.25; Noon is 0.5
Be careful when you enter “time values” into a cell. 12:00PM is considered “text.” You need to include a SPACE before the PM e.g. 12:00 PM in order to tell Excel that this is a “time value.”
Use this Keyboard Shortcut Ctrl+Shift+~ (Tilde) to see how Excel stores a “time value” in a cell.
When calculating “elapsed time”, use a “Custom Format” for the cell with the Formula E.g. [h]:mm
When you calculate “elapsed time” for cells that “cross midnight” you will get ###### as your result unless you use a formula like =MOD((End_Time-Start_Time), 1)
When you total “time values” that will exceed 24 hours (E.g. Hours worked in a week) format the cell containing the formula with a “Custom Format” e.g.[h]:mm

Find the Excel Video Lesson that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Filed Under: Everyday Calculations in Excel, Excel Date and Time Functions, Excel Training Tagged: Calculate Time in Excel, Ctrl+Shift+Tilde Shortcut, Custom Formats for Time in Excel, Excel Video Training, How Excel stores time values, Keyboard Shortcuts in Excel

Danny September 2, 2008 1 Comment

Learn how Excel calculates time

Today’s lesson builds on the knowledge we gained in the prior video. So, rather than calculate “Dates”in Excel, we will calculate “Time.” Excel stores both Dates and Times as Serial numbers in a cell. This enables us to calculate the amount of time between two dates or two times.

Learning how to properly format the cells that calculate time is crucial to achieving correct results. It is also important to observe the proper syntax for entering times in cells (00:00:00 PM) or Hours: Minutes: Seconds AM or PM)

Here are the steps to follow in this Excel Video Lesson:

Enter a Time function. e.g. =NOW() in a cell. Use the Keyboard Shortcut Ctrl+Shift+~ to reveal the serial number for that time.
Excel counts time beginning at 12:00 AM or Midnight. So, the serial number for 6:00AM is 0.25
If you only want to “Time Stamp” a cell, use the Keyboard Shortcut Ctrl+Shift+: (This entry will not update!)
The =TIME() function requires three arguments (Hours, minutes, seconds)
Be careful when performing calculations the show the difference between Start Time and End Time. If the End Time “spans midnight,” Excel will return an error unless…
You use an =IF() Function in this calculation =IF(End_Time<Start_Time, End_Time+1, End_Time)-Start_Time
To Increment a series of cells by 90 minutes use =Start_Time+TIME(1,30,0) and then copy the formula across the cells in the series.
Formats matter! To correctly calculate hours that exceed 24 hours: Use this format – [h]:mm in the cell that contains the calculation.

Danny Rocks “Training You to Excel”