An introductory video (youtube) by bytebodger describing the use and creation of pivot tables in Excel 2007.
Author Archive
Create a Pivot Table in Excel 2007
Sunday, August 17th, 2008Using Excel to Manage Simple Databases
Sunday, August 17th, 2008Here is a video from freetutorials (youtube) on using Excel to manage simple databases.
Bar Chart using REPT function
Monday, August 4th, 2008The REPT function repeats text a given number of times. For example, =REPT(“*”,10) repeats * 10 times in a cell.
You can use this function to create a simple bar for values in multiple cells. Here is a short video on YouTube:
How to configure Office 2007 to save files using the old Office 97-2003 format
Saturday, August 2nd, 2008Personally, I use Excel 2003 at work and Excel 2007 at home. I am constantly (as in 3 or 4 times a weeks) using Excel 2003 files in Excel 2007.
I found this tip at vista4beginners.com showing how to have Office 2007 automatically have files in the Office 2003 format: Configure Microsoft Office 2007 to save files using the old Office 97-2003 format
Pivot Tables – A powerful tool.
Monday, May 12th, 2008
At work I use pivot tables nearly every day. My work does not involve analyzing business or financial data and still I find pivot tables very useful in manipulating data sets, performing on-the-fly calculations, and preparing final reports.
So, what is a pivot table?
A pivot table is a data summarization tool found in data visualization programs such as spreadsheets (e.g. Microsoft Excel). Among other functions, they can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. Pivot tables are also useful for quickly creating cross tabs. The user sets up and changes the summary’s structure by dragging and dropping fields graphically. This “rotation” or pivoting of the summary table gives the concept its name. The term “pivot table” is a generic phrase used by multiple vendors. However, the specific form PivotTable is a trademark of Microsoft Corporation[1]. – Pivot Table wikipedia entry
See the ExcelBlog.net In-Depth page for more information on Pivot Tables.
Name Manager for Excel
Tuesday, April 1st, 2008Name Manager, now at Version 4, is a Excel Add-In that I find useful. Many times I have had to deal with workbooks with more than 100 names, many that are no longer valid. Name Manager helps manage such a mess.
If you are in need of a utility to manage defined names in your workbooks, this one is a must-have. List all names in your active workbook. Filter them using 13 filters, e.g. “With external references”, “With errors”, Hidden, Visible. Show just names that contain a substring. Show just names unused in worksheet cells. Edit them in a simple dialog or make a list, edit the list and update all names in one go. Delete, hide, unhide selected names with a single mouse click.
Available free at http://www.jkp-ads.com/officemarketplacenm-en.asp
Available for 97, 2000, XP, and 2003. And now a version for 2007.
Update (March 2008): Name Manager and Name Manager 2007 is now at version 4.1 (Build 599). New features: 1) When detecting unused names, all objects are searched as well as your VBA project. 2) A tiny but extremely handy toolbar to the VBE has been added. Selecting a name will paste that name at the current insertion point in your code!
Version 3.5 of Autosafe
Saturday, March 3rd, 2007An Excel file saving utility from Jan Karel Pieterse.
Autosafe.zip (28 February 2007, 150k, Downloaded: 362 times) Version 3.5 of Autosafe enables use in environments with long paths/filenames. The standard Autosave (note the spelling) utility that ships with Excel just saves workbooks at a set interval, overwriting the file on disk. This is not very convenient if you planned to leave the master file intact and save the changed workbook using a different filename. It also does nothing to simplify recovery of unsaved/changed documents after a system crash. This Autosafe utility creates copies of open workbooks at regular intervals in a separate (user-selectable) directory. It does not overwrite the master file(s), that is up to the user to do, using normal methods. As soon as a workbook is closed the backup copy is deleted from the backup directory. If an abnormal termination of Excel occurs, the backup copies remain on disk, and Autosafe finds them the next time Excel is started and presents recovery options to the user. Includes the following languages: English, Dansk, Deutsch, Español, Français, Indonesia, Italiano, Nederlands, Norsk, Hrvatski, Slovenščina.
Source: JKP-ADS Download page
ExcelKB’s Forum
Sunday, January 8th, 2006A new discussion forum on Excel from XL-Dennis : ExcelKB’s discussion forum.
What ExcelKB’s Forum is about:
• Discussions about software based projects and documentation, both in general terms and more specific projects terms – Best Practical Practice.
• Controlling Excel through automation with developing tools like classic VB and VB.NET/VSTO et al.
• Data exchange between databases (MySQL, SQL Server, Oracle, PostgreSQL et al), and Excel.
• Discussions about commercial and free components that improve application’s usability and performance, like ComponenOne, Iocomp and Robocx.
• Books and online sources that is relevant for the above subjects.There are some aspects that are important to me and therefore will be valid at ExcelKB’s Forum:
• No banners or ads will exist.
• No ranking system will exist and no titles/badges will be associated with any particular group of members.
• No Lounge will be available.
• Only real full names can be used for member’s accounts, i e no alias can be used.The ExcelKB’s Forum will not “compete” with the established online public core Excel forums or with Excel related Microsoft’s Newsgroup. ExcelKB’s Forum does not target to become one of the many general Q&A forums about Excel as it target only specific areas.
AJP Excel Information – Su Doku puzzle
Thursday, January 5th, 2006I read an article in the Denver Post this week regarding Sudoku Puzzles. I then completed several puzzles at websudoku.com.
You can create and solve sudoku puzzles using an MS Excel work book from Andy Pope: AJP Excel Information – Su Doku puzzle.
I also found a Sudoku puzzle solver at Office Online via Daily Dose of Excel.
More Sudoku linkes : http://www.sudokufun.com/ and http://sudoku.heebie.co.uk/
Google Maps In Excel
Thursday, August 11th, 2005An interesting idea. But how, beyond the neato factor, would one use an Excel sheet with an embedded Google map?
Here’s my first attempt at using google maps in excel (download below). Currently I can input an address and have a google map of the address displayed in Excel, with most of the cool google functionality.
Source – Google Maps In Excel
A very large Excel spreadsheet!
Wednesday, August 10th, 2005I wonder what the performance is like for the spreadsheet control that allows you to create a spreadsheet object with 262,144 Rows and 18,278 Columns.
Want more rows, more columns?
Think Excel is too small; Only 65,536 rows and 256 columns?Try the Spreadsheet control; a Microsoft ActiveX control.
Source – Office Tips and Hints Blog
Excel Watch
Friday, August 5th, 2005New Excel blog by John Walkenbach on Excel 12 – the next version of MS Excel.
Hey, A New Excel Blog!
This is the first entry in a new blog called The Next Version of Excel. The purpose of this blog is to be a useful (and interesting) source for information related to the upcoming version of Excel — currently known only as Excel 12.
At this point in time, hardly anything is known about Excel 12. But check back often. You never know what might get leaked out.
Source – Excel Watch
July 2005 Update of the Microsoft Excel 2003 VBA Language Reference
Tuesday, July 19th, 2005The Excel 2003 VBA Language Reference has been updated.
Welcome to the Microsoft Excel 2003 Language Reference. This reference contains conceptual overviews, programming tasks, samples, and references to guide you in developing solutions based on Microsoft Excel.
Publish date of this reference: July 2005 (version 2003)
Download the Microsoft Excel 2003 Language Reference
The reference contains the following sections:
What’s New Provides a list of new members by object and in alphabetical order.
Concepts Provides important concepts for developing custom Excel solutions.
Reference Provides reference materials for the Excel object model.
Source: Welcome to the Microsoft Excel 2003 VBA Language Reference
Migrating Excel VBA Solutions to Visual Studio 2005 Tools for Office
Monday, July 11th, 2005Microsoft Visual Basic for Applications (VBA) is the programming environment and language for Microsoft Office that has been available for several years. Microsoft Office 2003 Editions have a new environment for building applications, called Visual Studio Tools for Office, which is based on the Microsoft .NET Framework.
Microsoft Visual Studio Tools for Office, Version 2003, offered many advantages over VBA, but Microsoft Visual Studio 2005 Tools for Office enhances these features for even more user productivity.
This article analyzes many solution migration issues that you may face when using the Visual Basic language to directly migrate a VBA solution to Visual Studio 2005 Tools for Office. The
Source : Migrating Excel VBA Solutions to Visual Studio 2005 Tools for Office
Changing AutoFilter Drop-Down Arrow Colors
Sunday, July 10th, 2005Along with Pivot Tables, AutoFilter is one the Excel features I find most useful. I have always found that it a little hard sometimes to see which columns I am filtering on. Many times I have turned off the AutoFilter and then turned it back on in order to start from scratch.
Summary: When you use the AutoFilter capabilities of Excel (Data | Filter | AutoFilter), small drop-down arrows appear at the top of each column in your data table. The color of the drop-down arrows changes from black to navy blue if a filter is applied to the column. The color of the arrows cannot be changed, but there are workarounds that can be used to make filtered columns stand out better. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, and Excel 2003.)