Excel Basics Part IV: Number Formats

Posted on October 4 2011 11:05 PM by John Atten in Excel Basics, Microsoft   ||   Comments (0)

Having represented this site as a technology blog with a focus on programming and software development, I find myself starting off with a tutorial on Excel basics instead. Somehow, I thought it was going to be more glamorous than this . . . This is driven by my need to prepare a training for my staff at work, and in keeping with the DRY principle (Don't Repeat Yourself) I am killing two birds with one stone. Anyone hoping to read programming stuff is just going to have to wait . . .

 

 

 

 

 

If you do not have Microsoft Excel on you machine, or if you are using an older version (I use Excel 2010 throughout this series) you might consider purchasing a copy. You can find many deals on-line, or even support my efforts with this site by using the links above to buy directly from Microsoft. I STRONGLY recommend against purchasing software from Craigslist or EBay. There is significant risk of purchasing illegal or pirated software from those sites.

OK. If you have been following this little series, you are likely a beginning Excel User, or a glutton for punishment (or both). If you have not been following this series, but instead stumbled upon this entry, welcome to my series on Excel Basics.

We are going to step out of our examination of formulas and expressions for a moment to take a look at number formatting. While we touched on this subject very briefly in the first post, we will take a closer look now.

We will examine formatting from two perspectives. If you are an absolute beginner, you will learn how to apply formatting to cells, and a little about the various number formats themselves. If you are an intermediate or advanced user, you might find the section on the difference between the displayed format and the actual value stored in the cell to be a useful review.

For everyone’s benefit, let me put forth the following important reminder. Write this on your forehead:

It is the stored value that is used in calculations, NOT the displayed (formatted) value!

In this post

Formatting Basics (the VERY basics)

As we learned in the first post in this series, , all number values (including dates) are stored by Excel a double-precision floating point values. How, then, do we know what is a date, what is currency, what is a percentage, and so on?

Formatting, that's how.

Excel provides us with number formatting as a means to display a numerical value in a recognizable way relative to the context of use. If we are working with monetary values, it is helpful for our spreadsheet to display our data in a currency format. Likewise with dates and percentages. Exceptionally large (or exceptionally small) numerical values are often more effectively presented using scientific notation.

Let’s take a quick look at the basic number formats Microsoft Excel Provides. In the following image, the left-most column of cells all contain the same number value (3.33). The middle column describes the number format applied to the same value typed into the cells in the right-most column :

Excel-Number-Format-Samples_thumb11

Applying Formatting

To apply formatting to a cell or group of cells, we right-click within the selected cell(s). Select the “Format Cells…” menu item from the context menu:

You will be presented with a dialog which allows you to select from a list of pre-defined number formats (plus a few special and user-defined ones). Selecting an item from the list of available formats on the left will cause various options for the selected format to become available in the space to the right of the list.

Format-Cells-Dialog_thumb2

 

 

 

 

 

 

 

 

 

 

 

 

In this case, I am demonstrating the application of basic currency formatting. The options available for the currency format include number of decimal places, the desired currency symbol, and various means of representing negative numbers. The default values for all of these are what is displayed when you select the currency format (2 decimal places, US dollars (dependent upon your system localization settings) and the default representation for negative values. For this example, I am going with the defaults. When you hit the OK button, the 3.33 value we typed into the cell will be displayed as $3.33.

[Back to Top]

This is a good place to re-state the warning I posted in red at the beginning of this post:

It is the stored value that is used in calculations, NOT the displayed (formatted) value!

The hidden danger of number formatting in calculations

Open a new Excel Workbook. Then select the first two columns by clicking on the column headers (the gray rectangles with the letters A and B in them:

Select column A: Right-click and select "Format Cells"
Select Column A_thumb[9] Right Click Column A_thumb[8]

Now, do like we did in the first example, and format both columns as Currency. Use the default settings (2 decimal places, US currency, or whatever is native where you live). You will run into this format often in spreadsheet-land. Now select the first cell in column A (Cell A1), and type a simple formula to divide 10 by 3:

Enter the formula: Hit Enter:
Three Divided by Ten Before Enter Key_thumb[5] Three Divided by Ten After Enter Key_thumb[4]

 

Then, select the first cell in column B (Cell B1) and type the number 3.33:

Enter the number 3.33: Hit Enter:
Three Point Three Three Before Enter Key_thumb[1] Three Point Three Three After Enter Key_thumb[4]

Note that each cell now appears to contain the value of three dollars and thirty-three cents. Now we will use a logical test to illustrate why we need to be aware of how formatting can impact our spreadsheet design. We can compare the values stored in Cells A1 and B1 to see if they are equal. The following expression will return a logical result of TRUE or FALSE:

Enter the formula =A=B Hit Enter:
Compare A and B Before Enter_thumb[2] Compare A and B After Enter_thumb[1]

Now, why is that? I will say it one more time, for clarity:

It is the stored value that is used in calculations, NOT the displayed (formatted) value!

Excel treats the formula we entered into cell A1 as a value. The actual value of dividing the number ten by three is, in mathematical terms, 3.333333333333 . . . and so on. In performing the calculation, Excel carries this out to the limits of its 15 significant digits. While we see displayed $3.33, the value represented in the cell is actually 3.33333333333333. The number we entered into cell B1 is specifically 3.33.

Is 3.33 = 3.33333333333333?

No. The value in cell A1 is greater than the value in B1 by a little more than three one-thousandths. In other words, 3.33333333333333 > 3.33, and therefore, the expression testing whether or not the two values are equal returns FALSE.

A Side Note

If you select both columns again and remove the formatting (by setting the format back to "general") you will see that the number in cell A1 is displayed as 3.3333333333, which is only 11 significant digits. However, if you select the cell and look in the formula bar, you will see all 15 significant digits. Again, what is displayed in the cell does not represent the exact value stored in the cell.

Be aware of numerical precision and rounding in ALL your formulas

When using Excel, it is important to bear in mind that number formatting can obscure the mathematical precision of values. This is especially true of calculations involving division, but can also apply to other operations as well. Most of the time, this will have little impact, particularly when dealing with currency values in the context of everyday business transactions.

However, in certain calculations, precision which is masked by formatting can introduce errors (usually rounding errors or comparison errors such as illustrated here) which might invalidate your worksheet. Equally as often, this type of error is not immediately apparent.

Formatting is our friend, and it present our data in a familiar context which makes for visually appealing spreadsheets. Best, however, to pay attention to results which appear not to make sense. There is likely a reason for that.

[Back to Top]

In another post, we will examine rounding and rounding errors, and how these can impact your spreadsheets.

 

Posted on October 4 2011 11:05 PM by John Atten     

Comments (0)

The New Look and Feel of Windows Live

Posted on August 5 2011 12:15 AM by John Atten in Blogging, Microsoft   ||   Comments (0)

If you are anything like me, you have had an ubiquitous Windows Live Account kicking around for several years. Most likely, like me, you don't actually DO much with it, except use the Windows Live ID as a login mechanism for various Microsoft services on the web. I use it to access various MS technical subscriptions like Technet and MSDN. Some folks I know use Windows Messenger, but I'm not really a "chatty" kind of guy, so I never really got into it. Some people use it for Hotmail, but I don't see that many Hotmail addresses these days either.

Maybe it was just me all this time.

In any case, if you have not logged in to your Windows Live Account for a while, go do so. Really.

If you don't HAVE a Windows Live account, go make one: Windows Live/SkyDrive Page

Are you done yet? What did you think? Did you notice that , although the color scheme is still heinous, there are a few new items (ok, new to ME, but I am betting you haven't checked this out in a while either):

SkyDrive

Windows-Live-Top-Menu_thumb8

Notice that next to the familiar Hotmail and Messenger links there is now a link to SkyDrive? THIS is where the coolness begins. SkyDrive is, at its core, 25 gb of space that MS has decided you need, to do with as you please. Not earth shattering in and of itself, but when you consider that Dropbox gives away up to 8 gb out the gate (if you refer enough friends), and Amazon gives you five before you start paying, the 25 gb for free seems like, well, a good deal. Within the SkyDrive window itself (see image below) you have access to a comfortable folder structure which behaves as one would expect.

Office Web Apps

But it gets better. For one, you get MS Office Web Apps. In an obvious response to the increasing popularity of Google Docs, MS has decided to create the core Office functionality right there in your Live account. You can upload or create Word Documents, Excel spreadsheets, PowerPoint presentations, and OneNote Co-authoring and collaborations.

Skydrive-Window_thumb4

As you can see, not only can you work with the basic complement of office applications right there online (from wherever you happen to be), but you can also SHARE individual folders and files with others, (and others can share theirs with you) using the . Smells like Google Docs, but cool nonetheless. I don't know about you, but where I work, we use Office. And Office lives on all of my home machines as well.

When using the Office Web Apps, there are some small differences in how you access functionality. When you first open an Excel Workbook, you see what looks like, for the most part, a normal spreadsheet in your browser window. Your first inclination is to jump in and start editing, but you will find that nothing happens when you attempt to type in a cell. Before you can edit, you need to click on either the "Open in Excel" link, or the "Open in Browser" link:

Excel View Only Document

Note that from this view you can also share the workbook with others, and you can connect to data sources.

Clicking on "Open in Excel" link results in the expected behavior. The document opens on your local machine in the familiar Excel window (if you have Excel installed). I'm not certain what happens if you do not have Office on your local machine, or whether the the document would open in whatever application you have mapped as your default for the applicable file extension.

Clicking on "Open in Browser" results in a new page load, within which you will find your spreadsheet in a mostly familiar "Office-like" window. Within this window you can perform most of the basic editing functions, formatting, and data manipulations that would be available to you in a normal spreadsheet. However, many of the navigation behaviors and keyboard commands work just a little differently, and there are a few things which are NOT available. For example, the normal method of selecting one or more entire rows, then using Right-Click/Delete in order to vanquish a row grouping is not available. Same for columns. Instead, there are a pair of items on the faux "Ribbon" which provide this functionality (see image below). The same is true of the other components of the Office Web Apps suite – most of what you need for common formatting and editing is available within the browser window, but navigation and how you perform certain tasks will work just a little differently than expected.

Edit Spreadsheet in Browser

 

Synchronized Folders and Remote Access

You can also Sync folders between SkyDrive and your local machine(s), or among multiple machines. In order to do this you will need to install the Windows Live Mesh application on your local machine, which then also provides Sync for your Browser favorites as well. Syncing is optional, and you can sync multiple folders between multiple devices. The Live Mesh application also provides a remoting option, opening the possibility of accessing you local computer over the internet, through your windows live account. With remoting enabled, you can access your local computer from another machine which has the Live Mesh application installed, or through your Windows Live account website. Enabling remote access will allow you to access any of the applications and files available to your user account on the computer to which you are remoting in.

Note that there is a Windows Live Mesh for Macintosh, so Multi-platform users will be able to sync files between Windows and Mac machines with ease.

Photos

Photo AlbumsSkyDrive also has a folder for photos, which also acts as a folder gallery of sorts. When you select the Photos menu item on the sidebar, the right side of the SkyDrive work area displays your photo albums (see left). The UI layout here is obviously influenced by MS's new Metro UI design. Each of your albums is displayed as a large rDisplay Photosectangular icon,  within which a miniature slideshow presents the album's contents. The album title is displayed along the lower left edge of the album icon. When you have multiple albums with different content, the visual effect is very pleasing, as images from within each album fade in and out of view.

When you click on an album, the window is filled with the album's content – your images are laid out in Metro-UI fashion. If you re-size your browser window, the thumbnails rearrange themselves to properly fill the available space. Selecting a single image will expand the image to full size within the viewing area.

With 25 gb to work with, one could actually store a lot of photos on SkyDrive, making this a nice place to back up your favorite pics.

 

In addition to SkyDrive, Windows Live also makes available a package of supplementary applications called Windows Live Essentials. This includes the afore-mentioned Live Mesh, along with a few other gems. For instance, I am currently composing this post using Windows Live Writer, which is set up to interface with popular blogging applications. I find the interface preferable to the considerably more austere text editor window provided by the blogging application itself. There are also some applications for syncing your Hotmail and other email accounts, calendars, and contacts with Outlook, and Movie Maker for creating web-ready Movies

I found SkyDrive, Office Web Apps, and photo storage/management features pretty compelling. Sufficiently so that I have a renewed interest in my Windows Live Account. The additional functionality provided by the supporting applications is also handy. All told Microsoft has done a good job creating an easy-to-use synchronized workspace, with a great deal of storage, for free.

My only real complaint at this point is that there is no music functionality here. You would think that it would be a small step for MS to add a Windows Live Media Center to the mix, and call the package complete. As it is, there do not appear to be any plans to do so. While you can upload music files to SkyDrive just like any other file, there is no playback capability within SkyDrive. If you click on an .mp3 file, playback will occur through your local Media Player by streaming the content. This will definitely hinder MS ability to counter Apple's coming iCloud platform in the fall.

Pros

25 gb of storage (free)

5gb synced folders (DOES count against the 25 gb total)

Office Web Apps (free) work on line or with local Office Installation

Photo storage and management + nice UI

Sync and remote access features

Cons

No music playback

No music library, management, or store access

The Windows Live UI is, well, ugly. More of the stuff employed for the Photos interface, please . . .

 

Posted on August 5 2011 12:15 AM by John Atten     

Comments (0)

About the author

My name is John Atten, and my username on many of my online accounts is xivSolutions. I am Fascinated by all things technology and software development. I work mostly with C#, Java, SQL Server 2012, learning ASP.NET MVC, html 5/CSS/Javascript. I am always looking for new information, and value your feedback (especially where I got something wrong!). You can email me at:

jatten@typecastexception.com

Web Hosting by