Well, okay, maybe not, but there's no denying the fact that PivotTable experience is a sought-after skill in today's job market. In fact, the demand for PivotTable people has gone up even as demand for just plain Excel folks has gone down (source: Pivot Table Guy).
It isn't that hard to learn how to use PivotTables, either. If you have an Excel spreadsheet with rows and columns, you already have the source data necessary to generate a PivotTable. The table I was working with had 6 columns: Date, Location, Product, Channel, Quantity, and Revenue. To generate the PivotTable, select a cell anywhere in your table, go to Data > PivotTable and PivotChart Report (Insert > PivotTable in Excel 2007), and complete the wizard. This will create an empty PivotTable. Into the PivotTable you can drag and drop fields to create the final product. My table used Location for its rows, Date for its columns, and Revenue for its comparison data.
The main limitation of PivotTables is they only take up to 255 column values. That means that if you have sales figures for every day of the year, you can't display all of them at once. To fix that, right-click on the appropriate field (in this case, Date), select Group, and choose how you want to group the data. When dealing with dates, you can group by years, quarters, months, or days.
Want more detail than just revenue by location and date? Just drag and drop another field into the appropriate section. I added Channel to the columns, which breaks out the revenue by channel and then by date (which I've grouped by quarter to avoid exceeding the 255-column limit). Excel automatically calculates totals on all rows and columns, as well.
That's not quite good enough for me. I'm the sort of person that color-codes everything. That's where upgrading to Office 2007 finally has a benefit. There are the obvious formatting options (Home > Format as Table), which make the headers, footers, and totals different colors. In addition to that, however, you can add data bars to cells. Data bars are horizontal shadings in cells that show relative quantities according to the range of cells you select, allowing you to gauge at a glance which cells have high or low values. To add data bars, select the cells you want to compare to each other (all first-quarter sales by location, for example), go to Home > Conditional Formatting > Data Bars, and select the color you want. There are menu options (More Rules) to allow you to fine-tune the formatting. Voila, we have color.
Quote of the Day:
'Cause I get a thousand hugs
From ten thousand lightning bugs
As they try to teach me how to dance.
A foxtrot above my head,
A sock-hop beneath my bed,
A disco ball is just hanging by a thread...
--Fireflies, Owl City
Showing posts with label productivity. Show all posts
Showing posts with label productivity. Show all posts
November 06, 2009
September 26, 2009
Office Online
This is why I'm still using Office 2003:
Microsoft Office has been the de facto standard for office suites for, well, pretty much as long as it has existed. Need to generate a document with words, numbers, formulae, and/or pictures? It's a pretty safe bet you'll be using a Microsoft product for that. However, MS Office is a touch pricey and many people (and businesses, for that matter) cannot justify spending that kind of money every time a new version comes out.
Enter Web office apps. These are stripped-down, but free, MS Office-like programs that run inside your browser: Google Docs, Zoho, and the upcoming Microsoft Office Web Applications, to name a few. Due to limited time, I only tested the slide show apps included in each of these. (I also tested 280 Slides, which is a web app built on Cappuccino; there is no corresponding suite of office programs to go with it, but it's a neat program by itself.) So, how do they compare to each other and with the current non-Web app, MS Office?
Microsoft PowerPoint (Microsoft Office) is the standard. It does not run within your browser; it is a standalone program included in MS Office. It has transitions, animation, clipart, picture cropping, WordArt, everything. By default, files are saved in *.ppt format (except for the newest version of Office, in which case it is saved in *.pptx format). These files are readable in a multitude of viewers, and you can even use the "Pack and Go" wizard to include PowerPoint Viewer with your presentation. MS's decision to replace the menus with a ribbon interface in Office 2007, however, dramatically impacted its usability for longtime Office users who were used to where everything was in the menu system.
Features: 9/10 (could benefit from including more templates)
Ease of Use: 8/10 (Office 95-2003), 5/10 (Office 2007)
Ubiquity: 10/10
Overall: 9/10 (Office 95-2003), 8/10 (Office 2007)
Google Presentation (Google Docs) has the advantage of being run by the ever-ubiquitous Google. It has a word processor, a spreadsheet app, a slide show app, and a form editor. When you create a slide show, you can save it to your computer as a PowerPoint presentation (*.ppt). It has some decent slide templates. Entering text, pictures, and simple shapes is fairly straightforward. There does not appear to be any way to animate slide elements or have transitions between slides. If you want to have different font sizes associated with different list levels, you will have to manually change it yourself; you're also stuck with whatever bullet styles Google has assigned to each level.
Features: 6/10 (includes enough basic tools to get the job done)
Ease of Use: 8/10
Ubiquity: 8/10
Overall: 7.33/10
Zoho Show (Zoho) immediately runs into some issues. Unlike Presentation and PowerPoint, Show lacks any sort of right-mouse click functionality, meaning you have to find everything in the ribbon at the top. It allows you to change bullet styles, but some of the symbols may come across as question marks in your presentation if your browser does not support the proper font. Again, templates are limited, but that seems to be pandemic across all presentation programs. It will export to a *.ppt file on your computer.
Features: 6/10 (comparable to Google Apps)
Ease of Use: 5/10 (lacks context menus)
Ubiquity: 7/10 (lacks Google/MS name recognition)
Overall: 6/10
280 Slides has fewer templates and layouts to choose from than the other applications. You can have any bullet you want, as long as it is a large circle. Its features are simple and relatively easy to find, but limited. It does not support animation. It will export to a *.ppt or *.pptx file on your computer.
Features: 5/10
Ease of Use: 9/10 (no context menus, but good organization)
Ubiquity: 5/10 (lacks Google/MS name recognition)
Overall: 6.33/10
Zoho and Google are fairly comparable, so between those two I would choose Google, due to its ubiquity, (admittedly limited) context menus, and company stability -- Google is not going anywhere anytime soon. 280 Slides is good for tossing together a quick presentation on the go, but it lacks some of the features of the others. All in all, for basic needs, web app suites will get the job done, but for any higher-level functionality, looks like we'll be keeping our copies of Office for a while.
Quote of the Day:
see more Lolcats and funny pictures
Microsoft Office has been the de facto standard for office suites for, well, pretty much as long as it has existed. Need to generate a document with words, numbers, formulae, and/or pictures? It's a pretty safe bet you'll be using a Microsoft product for that. However, MS Office is a touch pricey and many people (and businesses, for that matter) cannot justify spending that kind of money every time a new version comes out.
Enter Web office apps. These are stripped-down, but free, MS Office-like programs that run inside your browser: Google Docs, Zoho, and the upcoming Microsoft Office Web Applications, to name a few. Due to limited time, I only tested the slide show apps included in each of these. (I also tested 280 Slides, which is a web app built on Cappuccino; there is no corresponding suite of office programs to go with it, but it's a neat program by itself.) So, how do they compare to each other and with the current non-Web app, MS Office?
Microsoft PowerPoint (Microsoft Office) is the standard. It does not run within your browser; it is a standalone program included in MS Office. It has transitions, animation, clipart, picture cropping, WordArt, everything. By default, files are saved in *.ppt format (except for the newest version of Office, in which case it is saved in *.pptx format). These files are readable in a multitude of viewers, and you can even use the "Pack and Go" wizard to include PowerPoint Viewer with your presentation. MS's decision to replace the menus with a ribbon interface in Office 2007, however, dramatically impacted its usability for longtime Office users who were used to where everything was in the menu system.
Features: 9/10 (could benefit from including more templates)
Ease of Use: 8/10 (Office 95-2003), 5/10 (Office 2007)
Ubiquity: 10/10
Overall: 9/10 (Office 95-2003), 8/10 (Office 2007)
Google Presentation (Google Docs) has the advantage of being run by the ever-ubiquitous Google. It has a word processor, a spreadsheet app, a slide show app, and a form editor. When you create a slide show, you can save it to your computer as a PowerPoint presentation (*.ppt). It has some decent slide templates. Entering text, pictures, and simple shapes is fairly straightforward. There does not appear to be any way to animate slide elements or have transitions between slides. If you want to have different font sizes associated with different list levels, you will have to manually change it yourself; you're also stuck with whatever bullet styles Google has assigned to each level.
Features: 6/10 (includes enough basic tools to get the job done)
Ease of Use: 8/10
Ubiquity: 8/10
Overall: 7.33/10
Zoho Show (Zoho) immediately runs into some issues. Unlike Presentation and PowerPoint, Show lacks any sort of right-mouse click functionality, meaning you have to find everything in the ribbon at the top. It allows you to change bullet styles, but some of the symbols may come across as question marks in your presentation if your browser does not support the proper font. Again, templates are limited, but that seems to be pandemic across all presentation programs. It will export to a *.ppt file on your computer.
Features: 6/10 (comparable to Google Apps)
Ease of Use: 5/10 (lacks context menus)
Ubiquity: 7/10 (lacks Google/MS name recognition)
Overall: 6/10
280 Slides has fewer templates and layouts to choose from than the other applications. You can have any bullet you want, as long as it is a large circle. Its features are simple and relatively easy to find, but limited. It does not support animation. It will export to a *.ppt or *.pptx file on your computer.
Features: 5/10
Ease of Use: 9/10 (no context menus, but good organization)
Ubiquity: 5/10 (lacks Google/MS name recognition)
Overall: 6.33/10
Zoho and Google are fairly comparable, so between those two I would choose Google, due to its ubiquity, (admittedly limited) context menus, and company stability -- Google is not going anywhere anytime soon. 280 Slides is good for tossing together a quick presentation on the go, but it lacks some of the features of the others. All in all, for basic needs, web app suites will get the job done, but for any higher-level functionality, looks like we'll be keeping our copies of Office for a while.
Quote of the Day:
Subscribe to:
Posts (Atom)
