Home Archives for Danny
March 9, 2011 Leave a Comment

My Best Practice Tips for Importing External Data Into Excel

I created this Excel Training Video after helping a viewer who was having difficulty getting the correct results in his Pivot Table. The problem, it turned out, was not with the Pivot Table. Rather, it was with the way his underlying data was stored and formatted.

When to Store Numbers as Text Values

Get External Data From Commands

External Data Commands

Specifically, he had combined the records from multiple workbooks into a master table. The data came from multiple main-frame computers. However, in some workbooks he had the “Invoice Number” stored as a “Numeric” value while in other workbooks it was stored – properly – as a”Text” value. Excel will treat Invoice Number 678910 stored as a number differently from the same entry (678910) stored as a text value.

Here is the “rule of thumb” to follow: Unless you will be using the SUM, AVERAGE, PRODUCT, MIN or MAX functions in a field, store numbers as “TEXT Values.” A short list includes fields for telephone number, postal codes, customer numbers and invoice numbers.

Start with the Correct Data

When you Import External Data Into Excel, start off on the right footing. Import the fields that contain Invoice Numbers, Postal Codes and Telephone numbers as “Text” values. In my experience, I have found that trying to format these fields “after the fact” does not give me the results that I was looking for. Getting the External Data fields correctly formatted at the Import Data step is the key to producing accurate and informative Excel reports.

“The 50 Best Tips for Excel 2007” DVD-ROM

Secure Shopping Cart
Secure Shopping Cart

This is one of the 50 Video Lessons that I offer on my DVD-ROM, “The 50 Best Tips for Excel 2007.” Click here to open a Secure Shopping Cart to purchase this DVD today. I guarantee your satisfaction. I will refund your purchase price if you are not 100% satisfied with my products.

Watch in High Definition mode on YouTube

Follow this link to watch this Excel video at DannyRocksExcels on YouTube.

My Best Practice Tips for Importing External Data Into Excel [ 7:07 ] Play Now | Play in Popup | Download (952)
Link to this post!
Filed Under: Excel Training, Import Data into Excel, Podcast Tips & Timesavers Tagged: , , , , ,
March 6, 2011 1 Comment

How to Sort Pivot Table Field Values in Excel

With a Pivot Table, you have more options and more flexibility when it comes to sorting your field values than you do with a regular Excel data set. However, many of my clients and viewers seem to get “lost” when it comes to creating custom sorts for their Pivot Table Field Valuess. So, let me demonstrate four ways to sort fields to suit your needs.

Sorting Fields in Pivot Tables

Sort Pivot Table Fields

Sort the Order of Months

 

If you create a Pivot Table for a Fiscal Year or,as in this example a Water Year, you will want to sort the Month Fields to match the starting and ending months in your FY. There are at least three ways to do this:

  1. Right click on the month that you want as your first month. Choose Move from the shortcut menu and select Move (in this case) Oct to Beginning.
  2. Another way to relocate a field value is to select the bottom part of the cell. When you see the 4-heard black cross, just move the field value to the new location. You will see a Gray Horizontal Bar as you drag the field.
  3. Most amazing way to move a field value  – and seeing is believing – is to go to the cell where you want to place the field value and just “type in the name of the field value. Incredibly, Excel moves the field values to accommodate the value that you typed in! This only works with a Pivot Table.

Sort Using a Custom List

You can create a Custom List and use that for sorting the values in your Pivot Table Field. After you create your Custom List (I show you how in this video) you choose the Pivot Table Options Sort command and then choose the direction – Ascending or Descending.

In Pivot Tables, Excel remembers your custom Field Value Settings when you move – or Pivot – field from Rows to Columns for example.

 After you watch this short five minute video lesson you will know how to sort field values in Pivot Tables to suit your needs.

Pivot Table Resources

Pivot Table Training Resources

Pivot Table Training Resources

I have published 90 Minute Video Tutorials to help you to quickly master Pivot Tables. No matter which version of Excel that you are using (Excel 2010, 2007, or 2003), I offer video training that is specific to your version. You can learn more about these resources at my secure online shopping website – http://shop.thecompanyrocks.com

Learn how to “Quickly Create Pivot Tables and Charts”

Related Excel Video Lesson

My YouTube Channel – DannyRocksExcels

Click here to watch this video in High Definition, Full-screen mode

How to Sort Pivot Table Fields in Excel [ 5:01 ] Play Now | Play in Popup | Download (1124)
Link to this post!
Filed Under: Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: , , , , , , ,
March 4, 2011 1 Comment

Create a Pivot Table for a Water or Hydrological Year

A viewer asked for my help in creating a Pivot Table Report that is based on a Water Year – also known as a Hydrological Year. In North America, a Water Year begins on October 1 and ends on September 30. If you use a Fiscal Year in your reporting, you will want to watch this Excel Training Video Lesson.

Pivot Table for Water Year

Pivot Table for Water Year

Add Two Fields to Source Data

In order for a Pivot Table to summarize non-calendar years and quarters you will need to have two additional fields in your source data:

  1. A Fiscal Year (Water Year) Field – Use the IF(), MONTH() and YEAR() Functions to produce this.
  2. A Fiscal Quarter (Hydrological) Quarter Field – Use the CHOOSE() Function to produce this.

Now you are ready to create your Pivot Table Report.

Group Dates by Month and Year

Begin by grouping the Date Field by Month and Year – not, in this case, by Quarter. Now, remove the new Year field that you just created and add in the Water Year Field and the Hydrological Quarter to the Pivot Table. Continue to arrange the Pivot Table to suit your needs.

Since there is no direct way to change a Calender Year into a Fiscal Year or Water Year, you must prepare your data prior to beginning to create your Pivot Table. Now that you have watched this video lesson, you know how to do this!

Learn how to “Quickly Create Excel Pivot Tables and Charts”

Related Excel Video Lessons

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

Create a Pivot Table for a Water or Hydrologic Year [ 9:37 ] Play Now | Play in Popup | Download (1249)
Link to this post!
Filed Under: Excel Functions, Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: , , , , , , , , , ,
March 2, 2011 Leave a Comment

Make it Easier to Display Large Numbers in Excel with Formatting Codes

In order to establish and maintain your credibility, the numbers that you store in Excel must be accurate and precise. You cannot compromise on this principle! In most cases, you will be working with large numbers – sometimes, very large numbers. And, this may present a problem when you need to present these numbers to your audience.

So …

It is essential that you distinguish between your need to maintain accurate “data” and your need to communicate the essential “information” that resides in your data to your audience.

Format Custom Numbers

Format Custom Numbers in Excel

When you are presenting the “essential information” that resides inside your database, you need to consider the needs of your audience.

Ask yourself these questions:

  • What essential information do you need to communicate to your audience?
  • What are the trends and variances that you see inside your data, that you want your audience to see, to understand, and take action on?
  • What level of precision do you need to convey to your audience so that they can grasp this information?
  • How can you make it easier for your audience to see – and understand – the trends and variances in your data?
  • What techniques can you use to present this information to your audience?

Apply a Custom Format to Numbers in Excel

You can answer these questions by learning how to apply “Custom Number Formatting” in Excel.

For example, instead of presenting this level of precision – 19, 699, 499, you can use this custom number format – 0.0,,”M”, to present this as 19.7M. From my perspective as both a presenter and an audience member, I prefer to see 19.7M. This makes it easier for me to process the information as an audience member. This makes it easier for me to present – saying 19,7 million is a lot easier than trying to say 19 million, 699 thousand, 4hundread and 99 dollars!

What is your preference?

Watch this short, focused Excel training video lesson to learn how to format and display large numbers in Excel so that you can make it easier for your audience to understand and act on the information that you are presenting.

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

Follow this link to watch this video lesson in High Definition, Full-screen mode on my YouTube Channel – DannyRocksExcels

Make it Easier to Display Large Numbers in Excel by Using Formatting Codes [ 6:29 ] Play Now | Play in Popup | Download (762)
Link to this post!
Filed Under: Excel Financial Tools, Excel Training, Format Cells in Excel, Podcast Tips & Timesavers Tagged: , , , , ,
February 27, 2011 Leave a Comment

Creating Combination Charts in Excel

A combination chart in Excel is not a single chart type. Rather, you create combination charts by combining two or more complementary chart types. The most common combination is a Column type for one series and a Line type chart for the second series. Combination Charts, in general, make it easier to spot or highlight the trends in your data.

Combination Chart

Excel Combination Chart

 Creating a Combination Chart

  1. Create a standard chart for your data.
  2. Select one series and then choose a different chart type for that series.
  3. Make formatting choices as necessary.

Adding a Secondary Axis for Data

When you are comparing data series that have a wide “gap” between them – e.g. comparing actual sales in a month (large number) – to Profit % in each month (small number), you will want to create a Secondary Vertical (Value) Axis in your Combination Chart.

Begin by selecting the series that you want to plot and Right Mouse Click to Format the Series on a Secondary Vertical Axis. I recommend that you add in Axis Titles for both the Primary and Secondary Vertical Axes.

Formatting Charts in Excel 2007 and 2010

Charting in Excel – beginning with Version 2007 – now offers quite a few professional graphic design elements. In this video lesson, I show you how to customize the presentation of your graphical image. You can make these changes with just a few mouse clicks! It is definitely worthwhile to learn how to improve the visual impact that your Combination Charts make.

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

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

Subscribe to or watch my – free – video podcast on iTunes.

Creating Combination Charts in Excel [ 7:46 ] Play Now | Play in Popup | Download (1056)
Link to this post!
Filed Under: Excel Charts, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , ,
February 22, 2011 9 Comments

Create Dynamic Chart Titles in Excel

A viewer wrote to ask for my help in creating dynamic chart titles in Excel. He has created a series of pivot table reports and pivot charts. He wants the titles for his charts to update dynamically. That is, as he selects a new filter for his pivot table, he wants the title in his pivot chart to match the value label in his filter.

Dynamic Chart Titles
Dynamic Chart Titles

 

Note: 

This technique will work for all Excel Charts and Chart Types. It is not restricted to Pivot Tables.

The solution is rather simple: You link the Chart Title to a formula.

Link Chart Title to Formula

Select a cell that is outside the range of your Pivot Table – let’s say cell H1. An example formula is:

=”Sales for Fiscal Year “&D1

In this case, cell D1 contains the label for the value that you are selecting to filter your Pivot Table Report and Chart – e.g. 2008 as your Fiscal Year.

Notice that in the formula, I include the “TEXT” inside ” ” (double quotation marks. I also use the & (ampersand) to join the text to the cell reference (D1).

Finally, select the Chart Title and then, in the formula bar type =H1 (where H1 is the cell that contains the formula that we just wrote).

As a finishing touch, you can “hide” the formula in cell H1 by changing the FONT COLOR to match the background color for that cell.

It is that easy! Try it yourself after you watch this short (6 minutes) video lesson.

Invitation to Visit My New Online Shopping Site

Shop for The 50 Best Tips for Excel 2007

Shop for The 50 Best Tips for Excel 2007

My new, secure, online shopping website – http://shop.thecompanyrocks.com – is now open. I invite you to stop by for a visit and get more information about the many resources  that I offer for sale including, “Learn how to “Master Excel in Minutes – Not Months!”

Watch Tutorial in High Definition

Follow this link to my YouTube Channel – DannyRocksExcels – where you can watch this video lesson in High Definition, Full-screen mode.

Creating Dynamic Chart Titles in Excel [ 6:09 ] Play Now | Play in Popup | Download (2578)
Link to this post!
Filed Under: Excel Charts, Excel Formulas, Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: , , , , , , ,
February 20, 2011 Leave a Comment

Download My Chart of Popular Keyboard Shortcuts for Word

Word Keyboard Shortcuts

Word Keyboard Shortcuts

I am pleased to make available to you – at no charge – my new chart of “Popular Keyboard Shortcuts for Word.”

Click on the link to download my chart of Word Keyboard Shortcuts as a PDF file. CR – Chart of Word Keyboard Shortcuts

I have organized the keyboard shortcuts into these categories:

Keyboard Shortcuts for Word

  • Basic File Operations
  • Undo, Restore or Repeat Actions
  • Move the Cursor
  • Select Words, Lines and Paragraphs
  • Use F8 Key in Selection Mode
  • Cut, Copy and Paste Selection
  • Delete Text and Graphics
  • Apply Formatting to Characters and Words
  • Find, Replace and Go To Text, Formatting and Document Location
  • Format Paragraphs
  • Insert Fields and Special Characters
  • Navigate in Word Table
  • Work in Outline View
  • Apply Styles
  • Switch Document Views
  • Review Documents and Create References

I took care to select and organize , what I feel are, the most popular keyboard shortcuts to use when working in all versions of Microsoft Word. Many people prefer to work without the mouse – keeping their hands on the keyboard. Most people, use a combination of mouse clicks and keyboard shortcuts when writing, editing and formatting their Word documents. Regardless of your experience working with Word, I think that you will discover one or two keyboard shortcuts that you were not familiar with.

I am always delighted to hear from my readers. Do you have a favorite keyboard shortcut? If so, what is it? Please add your comments below to share your experiences with all of my readers.

My Chart of Popular Keyboard Shortcuts for Excel

Earlier this year, I published a chart of the most popular keyboard shortcuts for Excel. Follow this link to download this chart – at no charge – as a PDF file. CR – Updated Chart of Popular Excel Keyboard Shortcuts

Download My Free 50 Minute Video Lesson for Excel Keyboard Shortcuts

I am pleased to announce that – when you click this link – you can get information on how to download my free “extended length” video recording where I demonstrate how and when to use Keyboard Shortcuts in Excel.

Link to this post!
Filed Under: Keyboard Shortcuts, Word Tips Tagged: , , , , , , ,
February 17, 2011 2 Comments

How to Find and Change Numbers Stored as Text in Excel

Whenever a client or a colleague sends me an Excel workbook I like to inspect it, to be sure that the “numbers are really numbers.” I will not get accurate results when I SUM them if there are some cells that appear to contain numbers, but really display a “number stored as text.”

Prevent GIGO

Prevent GIGO

This is an example of GIGO – “Garbage in delivers Garbage Out!” I take steps to prevent an outbreak of GIGO in my Excel worksheets.

Looking for Numbers Stored as Text

As a best practice, I use both the COUNT() Function and the COUNTA() function to see if there is a difference between the number of cells with numbers (the COUNT Function) and the number of non-blank cells (the COUNTA Function). In this example, I was expecting to see 31 cells with numbers in the range of sales. There were on 27. So, I need to find those 4 cells that contain “numbers stored as text.” 

If you have a keen eye – and some time to spare – you can look for cells with a little Green triangle in the upper left corner. Click on the message next to these “marked” cells and read the error message. Excel will offer to correct the error – one cell at a time. That is not an efficient way to proceed!

Use the ISTEXT() or ISNUMBER() Functions

An alternative way to find the cells that contain text stored as numbers is to use one of the “logical functions” ISTEXT or ISNUMBER. Each returns either TRUE or FALSE when evaluating a cell. You can then sort the data to group the TRUE results together and then change them – again, one at a time. This is also not the best way to make the changes.

Use Conditional Formatting to Highlight Cells with Number Stored as Text

I like to use the ISTEXT() Function as a “Rule” to apply special formatting to the cells that evaluate as TRUE with Conditional Formatting. Be sure that you select the cells that you want to evaluate before applying the Conditional Formatting.

Use Paste Special – Add

The quickest way to change numbers stored as text into real numbers is to use Paste Special – Add. First, select and copy a blank cell. Next, select your data range – in this case the cells with “Sales.” Then, right click the selection and choose Paste Special. In the dialog box select “Add.” Now – instantly – all of the cells with numbers stored as text have been changed into real numbers. You have stamped out GIGO!

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

Follow this link to watch this video in High Definition, Full-screen mode on my YouTube Channel – DannyRocksExcels

How to Find and Change Numbers Stored as Text in Excel [ 7:19 ] Play Now | Play in Popup | Download (1308)
Link to this post!
Filed Under: Conditional Formatting, Excel Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , , , , , ,
February 16, 2011 5 Comments

How to Find and Delete Duplicate Records in Excel 2007

It is easy  – much too easy – to allow duplicate records to creep into an Excel database. Fortunately, starting in Excel 2007, it is really easy to first find and then delete duplicate records.

Remove DUplicate Records

Remove Duplicate Records

With the price of postage climbing, you cannot afford to send duplicate copies of a mailing piece. Not to mention that doing so reflects poorly on your company’s organizational skills. Likewise, having duplicate account entries makes for extra work and confusion.

Use Conditional Formatting to Highlight Duplicate Records

Starting in Excel 2007, Conditional Formatting got a lot easier to use. One of the menu selections is “Show Duplicate Records.” Now, with just a few mouse clicks, you can format the records that show duplicate values in your data set. I like to use this technique prior to acutally duplicating records so that I can get a sense of the number of possible duplicate records in my data set.

Selecting Duplicate Records to Delete

As a best practice, I always make a backup copy of my data set before I actually delete records. This way, I can quickly recover from any accidental deletions of non-duplicate records. When you choose the command to “Remove Duplicates,” a dialog box opens to reveal each field in your data set. Generally, you want to select all of the fields to narrow down the list of records to delete. Since I have already previewed the possible duplicate records by using Conditional Formatting, I have a pretty good idea of how many records will be deleted. You do not see a preview of the exact records that will be deleted, so make sure that you have a backup copy of your original list in order to restore any “accidental” deletions.

These new commands and menus introduced in Excel 2007 make deleting duplicate records a very quick process. Use them – wisely – to save yourself time, confusion and money!

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

Watch this video lesson in High Definition, Full Screen mode. Follow this link to view this on my YouTube Channel – DannyRocksExcels

Secure Shopping Cart

Secure Shopping Cart

This is one of the tips that I offer on my DVD, “The 50 Best Tips for Excel 2007.” CLick here to open a secure shopping cart to purchase it.

How to Find and Remove Duplicate Records in Excel 2007 [ 4:26 ] Play Now | Play in Popup | Download (1082)
Link to this post!
Filed Under: Conditional Formatting, Excel Training, New in Excel 2007, Podcast Tips & Timesavers, Tips and Time Savers Tagged: , , , , ,
February 15, 2011 Leave a Comment

How to Convert from Military to Standard Time in Excel

Viewers and clients frequently ask me how to convert times displayed as military time (1700 hours) to standard time (5:00 PM). Many conpanies capture time using the 24 hour clock (military time) however, many end-users, particularly in the United States want to see time expressed as standard time with AM and PM designations. 

Time on Clock

Digital Time

Time Stored as Text Value or as a Number?

The first step in the conversion is to determine how the time value is “stored” in Excel. Is it stored as a “text value” or is it stored as a numeric value? This will determine how we convert military time to standard time. In this lesson, I demonstrate how to extract text characters, join text strings and then use the TIMEVALUE() Function in Excel to make this conversion. 

Use Text Functions and the “&” (ampersand)

Next, I use the LEFT() function to extract the two left-most characters from the text string (0100) and the RIGHT() function to extract the two right-most characters. In Excel, you use the “&” (ampersand) to join text strings. When you insert the “:” (colon) between the hours and the minutes, be sure to enclose it inside ” “(double quote marks). 

The TIMEVALUE() Function

In order to perform time elapsed calculations in Excel, you need to have time values “stored” as numeric values. You can use the “visual clue” – to which side of the cell is the time value aligned? Text values align to the left side of the cell; numeric values align to the right side of the cell. After you use the TIMEVALUE() function, be sure to format the numeric time values to meet your needs. 

If you import time values that are “stored” as numbers – they align to the right side of the cell – but are formatted as military time (13:30) all you have to do is change the fomratting to standard time (1:30 PM). 

Follow this link to watch this video lesson in High Definition, Full Screen mode on YouTube. My channel is DannyRocksExcels.

I invite you to subscribe to my video podcast, “Danny Rocks Tips and Timesavers,” available for free on iTunes. Click here to view my videos at the iTunes store.

Visit My New Online Shopping Website

I invite you to visit my new, secure shopping website – http://shop.thecompanyrocks.com – where you can view all of my products and “Learn how to “Master Excel in Minutes – Not Months!”

How to Convert from Military to Standard Time [ 6:40 ] Play Now | Play in Popup | Download (1134)
Link to this post!
Filed Under: Excel Date and Time Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , ,
« Previous Page
Next Page »