Tag Archives: Cloud Computing

View Excel Spreadsheets with the Google Docs Viewer

If you have a Gmail or Google Docs account, getting a quick look at a Microsoft Excel file is now supported with the Google Docs Viewer. Just click the View link beside the file name in your email and the Google Docs Viewer will open to give you a quick look.

Google Docs Viewer in Mail

Now you can send Excel files to people who have a Gmail or Google Docs account and they will be able to view your spreadsheet information. Not the greatest view of a spreadsheet file, but it’s better than nothing.

Google Docs Viewer with Excel File

In the Google Docs Viewer you have the option to save the file in Google Docs. The file is saved as an Excel file and you can then come back later to view the file.

There is also an option in your email to Open as a Google Spreadsheet, but this converts the Excel file to a Google Spreadsheet file and everything may not survive. Below is an example of a few Excel array formulas that did not convert.

Spreadsheet Conversion to Google Docs Formula Error

Most simple to intermediate spreadsheets — in size and complexity — will convert just fine.

Convert Coordinates for Your GPS with Excel

A few years ago I found myself just south of Paris, France one Sunday with a car and the inclination to do some sight-seeing. Since I was alone and don’t speak French my saving grace was Google Earth and a Tom Tom GPS navigation device.

Tom Tom allows GPS coordinates to be entered as a destination, but Google Earth lists those coordinates in a different format. In researching this article I found out that GPS coordinates can be presented in at least four different formats, making it difficult to understand the coordinates.

Google Maps view of the Eiffel Tower

Click the x to remove the Address box and show the interactive Google Map.


View Larger Map

Use Excel to Convert GPS Coordinates for Tom Tom

One of my destinations was to see the Eiffel Tower and Google Earth shows the GPS coordinates to be 48 degrees 51 minutes 32.64 seconds North and 2 degrees 17 minutes 34.90 seconds East. (Coordinate formatting shown as 48° 51′ 29.69″ N 2° 17′ 38.02″ E in Google Earth while holding the mouse over a position on the map.)

However the Tom Tom GPS device wanted coordinate input in degrees only, where minutes and seconds represent the fractional part. So I created a quick spreadsheet to do the conversion. (Click the picture to download the .xls file.)

GPS coordinate conversion

To convert from Degrees, Minutes, Seconds to a Tom Tom degree format, the math is:

Degrees+(Minutes/60+Seconds/3600)

This allowed me to enter GPS coordinates directly into the Tom Tom GPS device. Since I wasn’t familiar with the street names or the numbering system for locating addresses on the Tom Tom, this was much faster than trying to find the correct address in the Tom Tom street directory. The Tom Tom GPS was a loner from an associate so that was another factor in my decision to use GPS coordinates.

Note: Tom Tom allows different formats for GPS coordinates, this example just mirrored settings for the GPS I was using at the time.

Use Excel to Convert GPS Coordinates for Garmin

When I got back home to the USA and tried this with my Garmin it didn’t work because the required format for GPS coordinates was different. My Garmin GPS device wanted Degrees and Minutes only, with seconds being the fractional part of minutes. That formula is different because it requires some concatenation with an empty space between degrees and minutes.

Degrees & ” ” & Minutes+Seconds/60

So my Excel spreadsheet helped me convert numerous GPS coordinates for my trip to Paris.

I had a memorable time.

Eiffel Tower Paris

Note: If you’re wondering about the North and East designations, click this link to see how to read GPS coordinates.

Convert GPS Coordinates with Excel

Here’s an interactive worksheet that you can use to figure out some GPS coordinates for yourself.

To download the worksheet using this link.

Cross-posted at vlatte.net.

What Day of the Year is It?

Given the Date in this spreadsheet, a simple formula using the DATE Function will return the Day of Year. Go ahead and type a Date value in cell A2 to see how the formula works, the spreadsheet is embedded from my SkyDrive. (Date format is US, “m/d/yyyy”)

Click here if you can’t see the embedded worksheet.

The Day of Year Formula

The formula in cell B2 is:

= A2-DATE(YEAR(A2),1,0)

The DATE Function takes the Year from the Date you type in cell A2, and uses the zero (0) day of month 1 (January), which is the last day of the previous month. (I explained this in an earlier post).

The formula takes the Date you enter, and subtracts December 31st of the previous year to come up with the Day of Year.

You can copy the formula by double clicking cell B2, use Ctrl+A to select all the contents, and Ctrl+C to copy to the clipboard.

[UPDATE] Problems with SkyDrive

Apparently there are some problems with using the embedded worksheet on this web page. I added a public link to the file on Windows SkyDrive, but that seems to have some issues, depending on whether you use a Mac or Windows computer.

If you have problems with the embedded worksheet on this webpage, or with the web version on SkyDrive let me know in the comments what problem(s) you’re having, what hardware (Windows computer, Mac computer, iOS device – iPad, or other device) and what web browser (Firefox, Chrome, Safari, Opera, IE) you’re using. Thanks so much.

Microsoft Office 2011 Document Connection

Microsoft Document ConnectionOne of the neat programs you get with Microsoft Office 2011 is Microsoft Document Connection. It allows you to interface with your SkyDrive without going through a web browser.

Once you open the program and sign in to your SkyDrive account all your online files are accessible. You can open the files in Read Only or Edit mode.

Document Connection SkyDrive Files

In Preferences I set all my files to open in Edit mode so that when I double click on a file, Excel 2011 downloads, then opens the file so I can make changes.

Documnent Connection Download Files

Once changes are saved the file uploads to your SkyDrive account.

There’s also an option to upload a file directly to SkyDrive. And you can also get a URL link to any file on your SkyDrive.

I don’t know if a similar program is available with the Windows version because I didn’t purchase the entire Office suite of programs (too expensive). With Excel 2010 I have to launch my web browser to connect to my SkyDrive account in order to get access to my cloud storage files.

Files in the cloud are much easier to access and work with using Microsoft Document Connection. Take advantage if you have a Mac.

Equation Editors in Excel 2010 and Word 2011

Equation toolbar menu itemThe Equation Editor is new in Excel 2010 and designed to create a mathematical equation as a graphical object. I didn’t find it in Excel 2011 for Mac, but ran across an article showing they put it inside Word 2011. How very strange.

Here I’ll review the Excel 2010 version of Equation Editor first, then briefly compare Mathematical Equations in Google Docs, and come back to the Word 2011 version of Equation Editor.

Excel 2010 Equation Editor

The Equation Editor in Excel 2010 is difficult to navigate at first. The second time I tried creating an equation it got slightly easier, and the third time with the same equation (shown below) I seemed to almost get the hang of it.

Simple Formula with Equation Editor

The trick I learned was to just type the equation in and let the Editor do its thing in rearranging what you’re typing. On the Equation Tools Ribbon are two helpful selections: Professional and Linear (shown below). Playing around with these helped me understand how the Editor can manipulate simple equations.

Having said that, advanced equations will take some time to figure out, but this editor should handle most anything you can throw at it.

Entering an Equation

To enter an equation with the Equation Editor go to the Insert tab on the Ribbon, and click Equation. This gives you a text box with the words “Type equation here.” You’ll also notice two additional menu tabs on the Ribbon: Equation Tools, which becomes active, and Drawing Tools.

Equation Tools Tab 1

Left Side of Equation Tools Ribbon

There are varied Symbols readily available for insertion on the Equation Tools Ribbon tab (shown above). And several drop-down menu items that expand to show even more symbols for things like: Fraction, Script, Radical, Integral, Large Operator, Bracket, Function, Accent, Limit and Log, Operator, and Matrix. (Shown below)

Equation Tools Tab 2

Right Side of Equation Tools Ribbon

Clicking the Equation drop-down arrow will reveal several predefined equations that can be inserted as starting point for your equation. This how I started my first equation, which did me no favors. I fared much better just typing in the formula and letting the Equation Editor move things around.

One Annoyance

One of the problems with the Equation Editor is that if you stray to far outside the equation, yet still inside the text box, the Equation Tools tab disappears and you’re stuck with the Drawing Tools tab only. Maddening!

Google Docs Mathematical Equations

I looked at the new Mathematical Equations in Google Docs and it seemed easier to learn, but is not nearly as powerful as the Equation Editor. For example, the equation I created above was done in a Google Docs document with Mathematical Equations. I wasn’t able to do a strikethrough for the min text, like this: min.

Google Docs Equation Imported to Excel

And there wasn’t a good way to export this object out of Google Docs.

Equation Editor in Word 2011 for Mac

The Equation Editor in Word 2011 for Mac looks to be identical to the one in Excel 2010. You start an equation from the Document Elements tab on the Ribbon, by clicking Equation or the drop-down menu arrow and selecting a built-in equation.

The Equation Tools tab immediately becomes visible and active after starting an equation. I created the same formula with relative ease.

Equation Editor Word 2011

I kind of like the feel of this Word 2011 version better than what’s in Excel 2010. Go figure.

Excel 2011 for Mac – Installation, Preferences, and VBA

After reading all the great things about Microsoft Excel for Mac 2011 that were written before its release, I decided to purchase a copy. My primary goal was to have an Excel version on my MacBook Pro that allowed VBA macros, however the full Office software suite seemed to be a really great value.

I paid $174.99 USD for the Microsoft Office for Mac Home and Business 2011 – 1 Pack edition on amazon.com after pre-ordering and receiving a $25 discount from their price assurance guarantee.

Excel 2011 Software Installation

What you get with the Office 2011 Home and Business Edition:

  • Word 2011
  • PowerPoint 2011
  • Excel 2011
  • Outlook 2011
  • Microsoft Office Web App support
  • Messenger for Mac 8
  • Remote Desktop for Mac 2 (drive your Windows-based PC from your Mac)
  • Technical support 1 year

The installation took about 15 minutes. The program loaded, I entered the product key, activated and registered the software, then launched Excel, and got a welcome screen telling me all about the new features.

Whats New Excel 2011

And then I had to face the Excel Workbook Gallery, which is the default screen that appears when you open Excel 2011. Nice for all of 15 seconds.

At the bottom of this screen there’s a box you can check: Don’t show this when opening Excel, but I ignored it and went straightaway to a blank Excel workbook.

Excel 2011 Preferences

The first thing I wanted to check out was the Options, oops pardon my Windows speak, I mean Preferences, by using the menu selection Excel → Preferences or the keyboard shortcut Cmd+apostrophe ( ⌘ , ).

Excel 2011 Preferences

Within these preferences is standard stuff you’d find in a Windows version of Excel, but I want to review some of the settings for General, Edit, AutoComplete, Compatability, and Ribbon.

General Preference

In the General dialog box I unchecked the box beside: Open Excel Workbook Gallery when application opens so that Excel opens to a blank worksheet.

General Settings Change

Edit Preference

On the Edit dialog box, a new option for Excel 2011 is Automatically convert date system. As you can see in the Description box below, this option converts the date system of the source data to match the date system of the target workbook.

I’m not exactly sure how this preference option works and will be looking into it, but with Excel’s two different date systems (1900 & 1904) this setting becomes more important now that Excel 2011 and Excel 2010 can share workbooks on the web in Windows SkyDrive.

Excel 2011 Edit Dialog Box

AutoComplete Preference

In the AutoComplete dialog box the setting for Show the menu only after I’ve typed x letters (x set at 1, range 0-9) seems to be one that I’ll revisit soon. I’m not sure I want AutoComplete popping up after typing only 1 letter.

AutoComplete Dialog Box

CompatibilityPreference

On the Compatibility dialog box, under Transition, the Save files in this format: is defaulted to Excel Workbook (.xlsx) and can be set to a number of different things.

Excel 2011 Compatibility Dialog Box

Changing the default file format to Excel 97-2004 Workbook (.xls) might be prudent should you share files frequently with people using older versions of Excel.

Ribbon Preference

On the Ribbon dialog box, under Show or hide tabs, I noticed that you can drag them in the order you prefer. I added the Developer tab to the Excel Ribbon by checking the box for Developer.

Ribbon Dialog Box

The VBA Editor – A Quick Look

I had to peek at the VBA editor just to check it out. Going to the Developer tab and clicking Editor opened the VBA Editor, where I added a module and wrote a quick test macro, as you can see below.

Excel 2011 VBA Editor

Not a bad start.

Next I’ll compare the Ribbons of Excel 2011 and 2010.

A New Charts Editor – Google Docs Keeps Improving

Tuesday Google announced a New Charts Editor in Google Docs. This is important because Google continues to improve it’s spreadsheet offering in the cloud. And while in my opinion, Excel will always be the king of spreadsheets, Microsoft has to be aware that many younger people may very well use spreadsheets in Google Docs because it’s free. Hence, a new generation of people that may grow up without Excel.

There’s a short video on the Google Docs Blog about the new Charts Editor in Google Docs. They also show a few screen shots of some, but not all of the chart offerings.

  • Org Chart
  • Annotated Time Line
  • Motion Chart
  • Line Chart
  • Pie Chart
  • Column Chart
  • Scatter Chart
  • Sparkline
  • Gauge

The Sparkline chart isn’t shown in the blog article, but you can give the new charts editor a try at goo.gl/newcharts. I think their Sparkline charts are kind of big and bulky, but that’s just me.

However, I kind of like the Gauge chart.

Google Docs Charts Editor Gauge Chart

Let me know what you think.

Stock Price Formula in a Spreadsheet

Sadly, Excel doesn’t have a stock price formula, but Google Docs has just the thing: a GoogleFinance Function. It updates a stock price when you open a Google Docs spreadsheet.

Google Finance Formula SpreadsheetThe GoogleFinance Function has two arguments, symbol and attribute. The symbol argument represents the stock symbol for company or mutual fund. If the attribute argument is left blank, price is returned by default. Both arguments are to be enclosed in quotes.

A list of common attributes — high, low, volume, marketcap, etc. —  can be found here, just click on the link: View a list of common attributes.

There is a disclaimer shown that applies to the GoogleFinance Function.

Quotes may be delayed up to 20 minutes. Information is provided ‘as is’ and solely for informational purposes, not for trading purposes or advice.

It’s a shame that Excel doesn’t have a similar function, especially in Excel 2010, but I guess Excel is more about calculation than information.

Excel Files on the iPad or iPhone

Documents To Go® Premium lets me open and modify Excel files from my Dropbox folder right on my iPad or iPhone. I can also access and modify spreadsheets from my Google Docs account.

DocsToGo Premium Spreadsheet on iPad

Excel email attachments can be downloaded, viewed and edited. This is a big plus when traveling with the iPad, or iPhone for that matter.

I have an Excel file in Dropbox that serves as a reference and is password protected. Docs To Go® Premium opens this file and is the reason I purchased it.

The app has 111 functions and while this seems like a lot, one file opens in Read Only mode because several functions are not supported: COUNTIF, TEXT, SUBTOTAL. Not exactly uncommon formulas. I hope they expand the function list in the future.

In addition to online files, you can have local files on your device, which are accessible in iTunes. There’s an option to sync files with your computer, but requires a desktop app (Win & Mac) to sync over Wi-Fi.  I haven’t tried this out as of yet.

DocsToGo Premium File Locations

The non-premium version is available for $9.99 USD, but it doesn’t support file access to Dropbox. Documents To Go® Premium is $14.99 USD and seems pretty steep, but you also get Word and PowerPoint editing, and can view PDF and iWork files.

Excel on the Web – Another Spreadsheet in the Cloud

I decided to test the new Microsoft Excel Web App and tell you about my experience in creating a simple spreadsheet, give you a look at the interface, and share my initial opinion.

The Excel Web App – Getting Started

In order to use the Microsoft Excel Web App a Windows Live account is required. It’s relatively easy to sign up for an account and you get 25 GB of storage on a Sky Drive. You also get access to the rest of the Office Web Apps: Word, PowerPoint and OneNote.

Excel Web App vs Google Docs Spreadsheet

The Excel web app is similar to a Google Docs spreadsheet. By that I mean the spreadsheet is just a simple, basic offering. It seems like the Excel version has an advantage because the tools are more familiar and it’s integrated with the Excel 2010 desktop software.

My First Excel Web Apps Spreadsheet

My goal was to create a spreadsheet with a series of dates in one column and a formula in a second column showing the associated day. Entering and formatting column headers was simple, and similar to the desktop experience. Entering a date into a cell was also easy because it was just a simple manual entry.

And here’s where the Excel web app diverges from what I would normally do on the desktop version. In order to generate a series of dates I had to use a formula adding one (1) to the initial date. In the desktop version I would generate a list of dates with the series dialog box as I shared in a previous post. Easy peasy.

Not to worry, I’ll just use the fill handle and drag down the formula. But wait! You can’t grab the fill handle because there isn’t one. So I copy the formula, selected the range I wanted, and pasted the formula. Whew! That was a little more work than I’m used to.

Next I entered the TEXT formula in the second column and it worked perfectly. Yet I still  had to copy and paste the formula into the range below because there’s no fill handle for me to double-click and shoot it down automatically. Oh well, just feels a little rustic.

Next I clicked the Open in Excel file icon and the program asked me for my Windows Live ID and password. (I checked an option so I wouldn’t have to do this every time). Excel 2010 then started up and opened the spreadsheet file on my desktop.

I made a couple changes in Excel 2010 and then saved the file. This action saved it directly to the website and not my computer.

Using the Excel Web App, I noticed the changes made by the desktop program. I also became aware of an option to look at the Version History, which now consisted of links to two different files.

Doing a simple sort in the Excel Web App was not so simple. That’s because the only option is Sort & Filter as Table, which turns the sort range into a table.

Finally, I downloaded the file to my computer and tried to open it, but neither Excel 2010 nor Windows could recognize the file type. That was because the Excel Web App saved the file with an underscore (_) before and after the file name. Since the file extension was .XLSX_ I had to remove the trailing underscore so the file could be recognized and open correctly.

The Excel Web App Interface

There are several things you can do with Excel files in the browser. In the picture below Version history will take you to a page showing links to older versions listed by date and time. Clicking the More drop-down will give you three options: rename, download, and properties.

Excel Web Document File

Selecting the Excel Web Test file icon will open the spreadsheet in View mode. Selecting Edit in browser will open the same file in Edit mode.

View mode is shown in the picture below. Notice the active cell is B2.

Excel Web Document View

Below is the file in Edit mode. The active cell is B2, but the formula is now visible in the formula bar. You can also see the Ribbon interface.

Excel Web Document Edit

When you elect to Share a file the following screen pops up to give you a link.

Excel Web File Share Link

The permissions link (above) brings you to another screen (below) to manage who can access the file.

Excel Web File Share Options

Excel Web App Menus

Shown below are the menus you get in Excel’s web app. They’re considerably abbreviated when compared to the desktop program, but hey, this is a web app.

The File Menu

Clicking the “Where’s the Save Button?” will inform you the spreadsheet is saved automatically.

Excel Web File Menu

The Home Menu

This menu is one continuous bar that I cut in two so it would fit here on this web page.

Excel Web Home Menu 1

Excel Web Home Menu 1

Excel Web Home Menu 2

Excel Web Home Menu 2

Notice Sort & Filter as Table menu item shown above. You can’t really sort with out having the program put the data into a table, but at least they let you know beforehand with a pop-up box.

Excel Web Sort as Table Pop-Up

The Insert Menu

Excel Web Insert Menu

Excel Web Insert Menu

My First-Look Opinion

I am a hard-core Excel user. And while I don’t mind going through a learning curve, the effort required should make me more productive, e.g. slicers, power pivot. The problem I have with web based spreadsheets is that you have to go through a learning curve, but the end result is being less effective because the tools and features are much less than the desktop version of Excel and productivity suffers.

However, there is a place for the Excel Web App. I’m just not sure where it fits for me. The lack of features is mitigated by being able to use the Excel 2010 desktop program. So for me it’s a better choice for an online spreadsheet then Google Docs. And I really like the idea of 25 GB of storage. I just have to figure out how to make use of it.

Related Posts Plugin for WordPress, Blogger...