Excel macro solutions as building blocks with usage notes to help beginners and fairly advanced users work with Excel spreadsheets. Worksheet Formulas, VBA Macros, Backup & Recovery, Color Palette, Conditional Formatting, Event Macros, Excel Newsgroups, Response Time Considerations, Generating HTML, List of Functions and Subroutines, Shortcuts, Menus/Toolbars. – David McRitchie (over 200 Excel web pages)
[INDEX on/off site] [Search] |GG| [Newsgroups] [Outlook Express] [Posting/Netiquette] -- [backup/recovery] [Date & Time] [formula/formats/install macros] [pathname/headers/footers] [Proper/Caps/Upper] [Reformat/join/split] [Shortcuts] [Slow Response/Memory problems] [Toolbars]
Complete List of “Articles on this site”, below — newest additions: [Firefox]
[articles], [shorts], [other], [xlhelp], [faq], [whatsnew], [arto], [advtopics], [xltips], [tutorials], [vbatutorials], [mskb], [selfhelp], [forms], [style], [links], [downloads], [xl2html], [moredown], [API], [xlnewsletters], [ext], [desktop], [misc], [trademarks], [questions / contact],
Have a little fun with frames encompassing this page ... [View without Frames]
Create an additional vertical split or an additional horizontal split -- ;
Have a little fun with bookmarklets
This Site Was Mentioned in LockerGnome Mar 30, 2001 & Sept 5, 2002, and in PC WORLD, May, 2001
index index index index index index index index index index index index index index index index index
index to some of the onsite pages and off-site pages I reference
index index index index index index index index index index index index index index index index index
[What’s New] [Search]
Some information I have put together in using Excel spreadsheets. The Excel that I use is Excel 2000 and it is running under Windows 2000. . When I started these pages I was using Excel 7a (also referred to as Excel95 or XL95) on Windows NT 4.0 Workstation. Except for the use of hyperlinks added in XL97, most of things on my pages should run on XL95, XL97, XL98 (Mac), XL2000, XL2003, and XL2007. I will try to provide information for earlier/later releases when updating and when aware of differences. It is through the newsgroups that I have learned the most and it is probably through the newsgroups that you have come to my pages.
- Articles on this site.
- HELP within Excel.
- Frequently Asked Questions.
- Articles on Other Sites. Excel: Advanced topics, Tips, Lessons & Tutorials, Links, Downloads, API Code, Newsletters.
- File Extensions in Excel and other extensions.
- Microsoft Knowledge database (MS KB) - VBA
- Links to some Frequently Asked Questions (FAQ). I've listed some on this page and continue on another page.
- Miscellaneous Things unrelated to Excel change DOS session to white background.
Now that search engines have improved and can limit hits from a site, most of "My Excel Pages" are now found by search engines. At one time only the page you are looking at was indexed because if you don’t want to see this page you certainly wouldn’t want to see two-hundred more like it from a search engine. However, if you do a site search here then you do want to find most of the hits, so you can use the Google search in the upper right corner.
strings strings strings strings strings strings strings strings strings strings strings strings strings strings strings strings strings
Working with strings as text and in cells refers to pages onsite and off-site.
strings strings strings strings strings strings strings strings strings strings strings strings strings strings strings strings strings
- Important information. EXCEL newsgroups. Problem Solving.
- Displays, critical information with macro solutions
- Displays, How To ... Solutions
- Worksheet Solutions
- Problem Solving Subroutines (Macro and Function Solutions)
- Posting to Excel Newsgroups
- Hints to New Posters is regularly posted to the Excel newsgroups by Chip Pearson. [archived newposte.htm]
Posting to Excel Newsgroups (on my site) can be used to further explain some of the earlier items in Hints to New Posters how to turn off MIME. Also locate some commonly used acronyms: BTW, FWIW, HTH (Hope This Helps), TIA and another acronym you will see in these newsgroups: MVP.
- Excel Newsgroups and Searching Newsgroups
- Information on searching newsgroup archives at Google News to obtain solutions posted in previous Excel Newsgroup postings, and some information on searching the MS KB (Microsoft Knowledge Database).
- The following documents describe Subroutines and Functions that I have found useful and hope you will also find them useful. (keywords: SUB and FUNCTION )
- A1ONLY (macro only, no web page)
- A macro to copy the current sheet and retain only rows that have a perceived value in Column A.
- Backup your Files ★ Backup your files, always take backups
- Backing up your files is critical both at home and for business. Backup and Recovery sections.
- Build Table of Contents and similar listings, and working with Hyperlinks ★,
- Create a Table of Contents with hyperlinks to the other sheets in your workbook using the BuildTOC() macro. The BuildTOC macro will sort the names for you. (Hyperlinks were introduced in XL97). $$TOC is the suggested sheetname for this content. Additional macros relating to documentation and organization can also be found here including listing addins, functions and macros. Also have included information on creating and viewing hyperlinks individually and how to remove (delete) them singly or from a range. Includes subroutines: BuildTOC, SortAllSheets (sort worksheet tabs), hyperlink: (DelHyperLinks [delete hyperlinks], ConvertHyperlinks, MakeHyperLinks, RunSubFromActiveCell, LinkFix, LinkFix_GoTo, MakeHTML_Link) and Functions: (URL;); listings( Enumerate Addins (add-ins));
GoTo and Navigations: GoToCell, GotoSheet, GoToHyperlink, GoToNextSheet, GoToPrevSheet, GoToSub, GoToSubroutine, ShowTopLeft and ShowTopLeft5. Navigating to the Previous/Next Sheet Tab, makes looking through 100 sheets a lot faster (alternative is Ctrl+PageUP and Ctrl+PageDN).
An auxiliary page on how to Build a Summary Sheet describes how to create a summary sheet if the sheets in a workbook have a consistent format.
- Cell Comments ★
- Create a file showing cell comments in a book, and view results with your web browser.
- Clear Constants will clear constants in the selection area.
- This page is an example of creating a macro, and creating a toolbar icon to be used with the macro. Information here has been described in much more detail on other pages but this page is sufficient to accomplish these tasks for this macro. In fact you might want to first look at this page as an overview before looking at Proper, Formula, and Insert Row.
- Collections, a few collections
- Color Palette -- 56 Excel Colors ★ [additional keywords: colour, colours]
- Attempts to equate Excel ColorIndex values to RGB colors used in HTML. Includes formatting colors: [BLACK] [BLUE] [CYAN] [GREEN] [MAGENTA] [RED] [WHITE] [YELLOW] [COLOR1]..[Color56] and other color information. Color Sorting (on another page). Also see of coloring based on cell type.
- Color Stripes, Range, Conditional Formatting
- An example of coloring lines when the value in Column D (4th column) is negative. Some information on ranges. A quick look at conditional formatting available with XL97 and later.
- CSV (Comma Separated Values .CSV) files
- Conditional Formatting ★
- can be used for highlighting up to three ranges of numbers with color (text/background/borders/patterns) based on specified conditions. C.F. can be used for “color banding” to simulate the old green bar computer paper. You can use C.F. to highlight consecutive rows with same values; to highlight the row with the highest or lowest valued cell in a column.
- Date & Time ★
- Date and Time entries are stored as Date Serials. Use of =NOW() obtains the system date and time and is updated during recalculation. Additional date calculation examples including WORKDAY. Some Date formatting, windowing and Y2K.
- Delete Empty Cells, delete rows with cells of specific value,
- Del95HTMLempty macro is designed to clean up the mess that Excel 95 creates when pasting from a web page into Excel
Delete Cells/Rows in Range, based on empty cells Delete Empty Cells but don’t disturb rows, (also macros to Delete Empty Rows) DelCellsUp() will delete empty cells and cell with only spaces within range and move cells up from below even if not in range. DelEmpty is similar but deletes only blank (empty) cells. RemoveEmptyRows will remove rows that are entirely empty.
- Duplicates, aspects of duplicates
- in rows or cells from identification, suppression, removal or deletion.
- Elapsed Time Counting Techniques
- for Date Ranges: COUNT, COUNTIF, SUM, SUMIF, counting
- Email from Excel and from HTML, Start an Email for the recipients included
- within a selection of email addresses, additional examples include from a HYPERLINK Worksheet Function, and from HTML coding.
- Enumerate Addins listing of addins
- Errors in Worksheet Functions(#...!)
- Equal Length Cells per Column, generate text for posting from
a selection of cells, (HTML related)
- also has an example of a Pivot Table.
- Event, Change_Cell Events
- Worksheet events provide a means of automatically triggering a macro when something happens on the worksheet. (change, calculation, double-click, right-click, selection change, worksheet become active or is deactivated, and follow hyperlink).
- Excel Window, Workbook Window, Worksheet Window
- Minimizing, Maximizing and Restoring Excel Window frames and help with Loss of Blue Title-Bar
- Extraction of a Group of Digits and Dashes, posted by Harlan Grove. Examples
- use “Regular Expressions” in the LIKE operator. i.e. If Mid(s, i, 1) Like "[!-0-9]"
Also see case sensitivity within “Proper and Other Text Changes”.
- FillEmpty, Fill in empty cells
- Fill in empty cells with the content of the cell above it, providing the cell above is also within the selection range.
- Fill-Handle, Filling in an area, Replication and use of the Mouse ★ (fillhandle)
- The fill-handle is one of the most powerful tools in Excel, and normally alleviates the need to type in formulas similar to the previous lines. Additional techniques using the mouse to move rows or columns; and insert blank rows or columns all using the cursor in combination with the ctrl, shift, or alt keys. (related: shortcut keys, Insert Rows)
- Fit Print to Page, and Adjustments to Layout
- Font Information
- User Defined Functions: ISBOLD(cell), FontList, changing the Font used in cells from Courier typefaces to Times New Roman. Also see...
- Font Tables as Rendered by your browser for symbol, webdings, wingdings,
- wingdings 2, wingdings 3. (Also see Symbols and Unicode.htm).
- FORMULA ★, display, in use in another cell
- Initially describes how to install a macro, and a function, and how to invoke a macro and a function. Describes how to display the formula used in another cell, and how to display the formatting used. This information is particularly useful for debugging and for preparing printed documentation of formula, format, and font information used directly on the spreadsheet. Includes how to tell if another cell is a formula or an entry. Quick method of viewing both formula and format utilizing a msgbox without going to the Format menu. Functions include: GetFormula, GetFormulaD, GetFormat, HasFormula, BoldSum, FontStyle, FontInfo, UseFormula, plus subroutines including FormulaBox to show cell information, and sheet statistics. Includes some worksheet functions (Large/Small). Includes instructions to install a subroutine (SUB) or function and how to add descriptions to the Function Wizard. Fractions rounded to 1/4, 1/8, 1/16, 1/32, 1/64, 1/128. Format example includes formatting for India/Thai currency -- Rupees, Rs., Paise, lakhs, crores, Bhat.
- Fractions and Formatting of Fractions
- This is basically a subset of the Formula page.
- Fractions rounded to 1/4, 1/8, 1/16, 1/32, 1/64, 1/128
- Bernie Deitrick expanded an initial contribution concerning Carpentry Measurements on the Formula page.
- Freeze Panes and Rows/Columns to repeat.
- When viewing a sheet we usually want to see the descriptive column and row headings anywhere on the sheet whether display or on the printed page. Also see Headings for Columns, and Page Numbering page.
- ghosting.txt “sticky keys” like problem where cells don’t unselect properly.
- Actually what is seen are cells from other sheets ghosting through. Not to be confused with “Sticky keys” which is valid disability keyboard option that allows use of sequential use of single key instead of combinations for shortcuts (i.e. Alt+Enter is a combination.
- Gridlines, and Borders covers loss of gridlines or borders.
- Headings for Columns, and Page Numbering page.
- Highlight Changes and identify author of change.
- //See warning about not being able to access macros//
- HTML, conversion from Excel, and Browser Bookmarks (Favorites).
- IF THEN in worksheet functions and in VBA,
- can be little tricky to those just starting Excel or VBA.
- InputBox and MsgBox
- INDEX to my onsite pages and off-site pages I reference. INDEX
- Plus a few of particular interest even if not referenced on my pages.
- INDIRECT Returns a reference indicated by a text value
- Used to find the value of a cell pointed to by the address in another cell.
- Insert Row using a Macro
- The macro described will insert row(s) below a selected row or cell. The advantage of using a macro to insert rows is that the formulas will be copied, but not the data; providing a more reliable method of inserting lines than simply inserting a row and then dragging a row with formulas and data into an inserted empty row. Also included is a fix for right minus sign (right negative sign) that you are likely to encounter on bank statements.
- LASTCELL, Reset Last Cell Used
- Attempts to provide additional information concerning eliminating unused rows at end and unused columns to right of sheet beyond what can be seen in Q134617.
Macros: CleanUpLastCells attempts to fixup last cells on all sheets, MakeLastCell to force the active cell to become the lastcell, and QueryLastCells to identify sheets with large used cell area.
- Logical Expressions - AND(), OR(), NOT()
- Logo or graphic into a header or footer
- Headers and footers do not support placing a .BMP graphic directly into a header or footer, but there are some alternatives such as using the placing the logo on the first row of the spreadsheet and incorporating spreadsheet rows as part of or as a replacement for the header. Something that works in both headers and footers is to use a typographical font symbol. You will probably find a technique that you can use. Also of interest in how to incorporate column headings on printed pages.
- Mail Merge, Printing Labels using Mail Merge with data from Excel
- Excel cannot print postal bar codes but MS Word can print postal bar codes and can obtain data from an Excel spreadsheet. One is expected to use Mail Merge even for just printing labels. Includes a macro, RepeatRowsOnColumnA, to set up for printing multiple labels with same content. (Mailto: in HTML)
- Make hundreds of HTML files
- from a list of filenames in Column A and HTML skeletal code from column C. Example uses Basic commands to Open/Close and Write/overwrite to file. View sample in Notepad and in your web browser(s). Much of the basic writing to a file is similar to the more complex Menus
- My page on documentation of workbooks includes links on creating Menus, see Related.
- Merge and Unmerge Cells
- MergeRxR will merge a large group of cells one row at a time so that rows remain distinct but the columns are merged -- useful for forms designed on a spreadsheet. Macros include: MergeRxR, MergeRxR_join, MergeCxC, UnMerge, UnMerge_Selected.
- Miscellaneous Worksheet examples
- Mixed worksheet function code segments not otherwise categorized.
- Mouse Operations, Use of Mouse and keys to move data, and insert rows or columns,
- and to jump around
- Multiplication Table Quiz, for 12 x 12.
- Done entirely with Worksheet Event macros.
- Name and Address labels (macro code) to
- convert 1-up labels to Spreadsheet format on a new sheet. Description can be found on my Snake Columns page. (also see Mail Merge)
- Names, name labels in the workbook, addresses problems with creating names.
- Navigation, Navigating within a Sheet and Around a Workbook (onsite material).
- Newsgroups and Searching Newsgroups, Excel
- Some information on searching for information using Google Usenet Advanced Search to search Excel Newsgroups, and some information on searching the Microsoft Knowledge Database (KB).
- Number conversions.
- Conversion of numbers which appear correct but are not validly interpreted in Excel. Numbers with a right minus appear correct but are treated as Text. US numbers use period as decimal fraction separator, and commas as thousands list separator, which is the reverse of many other countries. ReenterAsNumber converts text to numbers or date.
- OFFSET, use of to maintain formulas -- Specifics on use of OFFSET
- (continuation of InsertRowsAndFillFormulas macro)
- Paste Rows from one sheet to another and Paste / Pasting
- Pathname in heading, footers, cell ★, includes formatting a date in the footer
- Excel does not provide the same level of support for headings and footers in Excel as in MS Word. Included here are some things that can be done using Visual Basic (VBA). The main support offered is in putting the fullname in a footer , but material also is included to tell how to put this information into a cell and onto the titlebar.
- PDF with Excel
- Transferring data from PDF files with Adobe or Adobe Reader into Excel.
- Posting to Excel Newsgroups, Usenet Netiquette,
- particularly in the Excel Newsgroups, how to turn off MIME/HTML posting, and helps to explain some of the Hints to New Posters (Chip Pearson).
- Previous, Return to Previously Selected Sheet like a hyperlink not as
- Printing with macros
- Printing in Reverse order, printing specific range per sheet.
- Proper, and other Text changes ★★
- PROPER, LOWER (LCase), and UPPER (UCase) functions. TrimSUB subroutine to TRIM cells in a selection. Stresses efficient coding, cell selection and restricting processing to specific kinds of data with SpecialCells by types(text, numbers, formula, constants). Proper Subroutine includes additional code to correct certain surnames beyond builtin features of Proper Worksheet Function, and because these are macros, the changes are done in place. [capitalization, lettercase, lower case, lowercase, lower-case, proper case, title case, upper case, upper-case, uppercase]
- Properties, Title/Author/... Property, UserID, etc.
- Excel Maintains: Title, Subject, Author, Keywords, Comments, Last Author, Application Name, Last Print Date, Creation Date, Last Save Time, Security, Category, Manager, Company
- Push down values in Column if not lowest in row (pushdown.htm)
- Reformat or Rearrange Data in Columns (join.htm) ★
- Macros especially useful for reformatting name and address listings, and for creating test data. Rearrange columns by splitting, joining, or reversing columns of data. Join() can be used as a reversal of a correctly executed text to columns. Lastname() can be used as a put lastname first is not already done. FixUSzip5() corrects US 5-digit zipcodes by conversion to 5 digit numbers as text. ReEnter() equivalent to F2 then Enter, includes some specialized versions (also see Number Conversions page). SepTerm() can be used as a more limited version of text to columns that only separates from the first word. SepLastTerm() separates last word from beginning of text. TrimALL() is used to TRIM leading and trailing spaces from selected range. ReversI() is used to reverse the order of items in a row, column, or range. RotateCW() is used to rotate a range of cells 90 degrees preserving formulas and formats. Selection area must include cell A1. MarkCells() is used to create test data within the invoked range. MarkSepAreas() includes cell address and area number for creating test data across multiple ranges. i.e. A1-1, B1-1, B2-2,C2-2,D2-2. Additional material includes FillSequence() numbers cells 1,2,3, etc. for selected range(s), a note on use of fill handle for creating sequence, and creating a sheet with short cut keys for reference (see results at Shortcut Keys in Excel 2000 through 2007). ReproduceActive() Reproduce Formula in ActiveCell to selected ranges(s). Also see Swap rows/columns.
- Region, Simulating manual usage
- Macro Record does not generate range codes, so coding needs cannot simply match what is done with keystrokes, such as the arrow keys.
- Replace Worksheet Function and Replace in VBA
- Replicate cells with INDIRECT formulas,
- referring to same range on another sheet, so that cells will always refer to a specific source cell location even if some rows/cells/columns are deleted on the source sheet.
- Resize Row Height - problems with wrapped cells
- Right-Click Context Menus in Excel
- additions include copying and pasting formulas, autosum, column total, clear constants, and get formula.
- -- also have a pages for Internet Explorer Right-Click Menus, and for Firefox.
- SAVE problems in Excel
- Problems related to LAN. Some solutions to space problems can be seen in Slow Response and Memory Problems in Excel which follows.
- SAVEAS, Save each worksheet as a separate workbook
- Saving each worksheet separately to multiple workbooks. You can use this subroutine to see how large each worksheet really is.
- Shapes, listing of all shapes in a workbook, and working with shapes,
- including list buttons and shapes on the worksheets (GetShapeProc); deleting all shapes (delShapesOnSht), selectively (delShapesSel); Selecting shapes (selShapesOnSht), Identify (IDthisShape), and how to redimension a shape.
- Sheets (copying, new sheets, renaming, selection) and a similar page for Cells
- related coding. Some VBA coding for sheets with a few worksheet coding examples, also see BuildTOC, and Pathname. Cells related coding.
- SHELL invoked from VBA, i.e. invoke another application like Internet Explorer
- Shortcut keys in Excel 2000 - 2007 ★ (Excel Keyboard Shortcuts shortx2k.htm)
- An HTML page reference for keyboard shortcut keys that you can use your browser’s FIND command to help find the shortcut you want/need. Ctrl_A macro is a fix for Excel 2003 problem with Ctrl+A and contains an example of Application.RecordMacro for use in embedded macro to generate code otherwise missing. (related: Fillhandle and Mouse)
- Slow Response and Memory Problems in Excel
- Many things can slow down Excel, and affect performance, one of the main reasons is Journaling (logging) of file opens and closes that began in XL97 across all of Office 98. [code, efficient, fast, memory, slow, speed, response, sub]
- SnakeCols, Snaking columns allows you to print several columns on one page and is a frequent request
- on the Excel Newsgroups. The feature is not supported in Excel, but is easily done by pasting your Excel columns into MS Word. I have described how do this using Word retaining your column headings as well as describing my own incomplete macro should you want to do this completely in Excel.
- Sorting, notes on, The difference between text cells and number cells is a frequently misunderstood aspect.
- Excel does not sort in a strictly ASCII sequence, it has it’s own sequence, but the biggest shock even to those aware of both ASCII and EBCDIC will be the treatment of cells with numbers as opposed to text cells.
- Sort TCP/IP addresses and Chemical Names.
- Status Bar, parts of status bar, and parts of Excel Screen ★
- Stocks, A lot of postings on Stock quotations.
- My own advice is to purchase a financial package, but there are some rather interesting things you can do with Excel accessing online quotes.
- Strings ★, Manipulating, rearranging, and extracting from strings.
- topics include searching within strings, case-sensitivity, generating check protection wording from numbers. Many of my pages tend to deal with strings and I have tried to gather the important points concerning strings from my site and from links to other sites.
- Primary example uses SUMIF to provide subtotals by date. Syntax and additional examples are also included.
- Summarizing Data, and Auto Filter (an Overview)
- Summarizing Data Examples: Auto Filter with Hotel Room Availability Example, Pivot Table, Sub Totals, Counting/Counts (COUNT, COUNTIF, SUM, SUMIF, SUMPRODUCT), Conditional Formatting, Sum of Visible Cells. Filter Test, AutoFilter and Hidden Rows/Columns
- Swap Rows, Swap Columns, based on a selection with exactly two areas.
- Templates, book.xlt, sheet.xlt, and beyond, customized defaults for future
- workbooks and sheets
- Toolbars, Custom Buttons and Menus
- After messing up my toolbars more than once, I would recommend printing a copy of your current toolbars as seen on your Excel window. Refer to the article for more information of changing and restoring your toolbars. Also for creating a Customized Button. Have added information on menus in Excel 2000 which has changed from methods used in XL95.
(These like toolbuttons are 16x16 bit images). Restoring an .XLB file from a backup may require reentering the assigned macros, see BarHopper macro to recursively reassign the macro names, seems to reset something internally to get them functional.
- Validate / Validation 2001-04-29
- Some validation examples for use with the Data, validation menu. Worksheets can be protected against incidental damage to the integrity of the worksheet by limiting access to some cells.
- Visual Basic Editor (VBE) Window, fixing docking back to vertical. 2001-03-29
- VLOOKUP ★
- Example for the VLOOKUP Worksheet Function using a lookup in an Estate Tax Table.
Paint Coverage Example, Paint
- Worksheets in VBA Coding and in Worksheet Formulas. Examples of cell references to another sheet or another workbook. HYPERLINK worksheet formulas.
- Code for WordCount() was derived from John's tip page look for something related to count or counting (pages are different used to be extract element.
- Some coding mostly from Harold Staff to convert a group of cells, or a picture to a .GIF file.
- XL2HTML and XL2HTMLX, Excel to HTML conversion 2000-07-02
- Create HTML code from a group of Excel cells. Also see my HTML section on my Excel to HTML web page for more HTML things. There are two macros here of importance: XL2HTML to convert selection to HTML with text color, and XL2HTMLx to convert selection to HTML with shaded row and column headers. Shading from Conditional Formatting is covered in these macros.
Short Topics and Examples may or may not be related to Excel (#shorts)
- Avery Labels, referenced in MS Word. [mailmerg]
- Bus Schedule, create a new sheet with PM times in boldface. [datetime]
- Cell comments, printing, by column then row. [ccomments]
- Comparison, of Excel workbook/sheets and flat files. [strictly links]
- Copy VBA [mostly links]
- Country codes used in Excel.
- CRLF -- Lines Split by Carriage Return, Line Feed
- Getting Started with Macros (simpler) approach to installing/using macros, while Install Macros and User Defined Functions has more comprehensive information.
- Hidden Worksheets and hiding.htm Save Prompts, Title bar, Worksheet Tabs.
- Page Breaks and Page brks
- Problems: Grayed Out Help, Slow Response
- Short Topics: Random RAND(), Read only, Recorded, record vba, Scrollbar, thaibhat.htm
Some Other pages on my Site (#other)
- AOL, Experience with AOL 4.0 (16-bit) using a Windows NT 4.0 system
- Office 2000, my experience in installing - Progress report
- Bookmarklets for Internet Explorer, Netscape, Mozilla regain control
- over unwanted webpage features like background images, bad CSS coloring schemes, and do neat things like sort HTML Tables (Mozilla), Combining “highlight links”, and “int/ext links” really makes links show up. Click on both links on the left to see the change and differentiation between internal or external links. (Use F5 to reset page)
- Firefox, notes on installing, customizing, and use of the Firefox web browser with blocking ads, use of all of the fonts.
- Free Web Access with Free Email
- for those who aren’t online much. Also includes information on setting up Outlook Express, and some “must have software”.
- INDEX to my pages, index index index index index index index index index
- plus additional links to articles in postings, on other sites, and some short descriptions not necessarily mentioned or related to others on my site.
index index index index index index index index index index index index INDEX
- Internet Explorer and Outlook Express, IE6 keyboard and mouse shortcuts, IE6 right click shortcuts, bookmarklets, OE6 shortcuts
- Outlook Express 5.0 through 6.0, some techniques in using.
- Includes information on combating computer viruses and spyware. (virus)
- Running Tasks list found in Microsoft System Information (MSInfo32.exe)
- Symbols for HTML use Euro(),wingdings, webdings, and installing CharMap macro and toolbar icon
- also a page on Fonts showing your default font, webdings, wingdings, wingdings 2, and wingdings 3
[Site Search -- Excel]
My Macros are Above (UP to articles) .
- Worksheet Functions and VBA, HELP (onsite)
- Where to find help for Worksheet Functions in HELP, now that we are essentially working without printed manuals. Also provides some help on finding the VBA material in Excel Help.
Also see my page on Links to Frequently Asked Questions relating to Windows 98/NT etc., and some additional EXCEL FAQ sites as well.
Capacities and Limits in XL95 (or for your own Excel)HELP --> answer Wizard --> length and columns and rows --> Tell Me About ...Excel FAQ
Microsoft Excel for Windows specifications (Check your own HELP file for "specifications")
The maximum rows was increased to 65,536 in XL97 from 16,384 in XL95. The number of columns 256 remained unchanged. VBA coding should not refer to a specific number of rows or columns instead use cells.rows.count and cells.columns.count – The Excel 2007 (Excel 12) grid is 1,048,576 rows by 16,384 columns (2006-03-09, David Gainer's blog). I have been using cells.rows.count in VBA since mid 2000 once I found out how to avoid the constants.
- Application workspace specifications
- Worksheet and workbook specifications
- Charting specifications
The FAQ list for Newsgroup: comp.apps.spreadsheets can be found on the Internet:
- Developer FAQ (archived from Baarns) -- Excel, Word, and Access. [or here] (archived from Baarns) -- Excel, Word, and Access.
- Microsoft Excel for Windows Frequently Asked Questions (KB article)
You might want to start at Popular Topics for Microsoft Excel for Windows.
A frequent question is where is the FAQ for this newsgroup. Usually such lists are maintained by the list owner. The list owner for the peer to peer Excel newsgroups is Microsoft. The KB contains answers to all sorts of questions posed to MS not just questions that have appeared in newsgroups. You may find access to the MS KB restricted as you must register (dead link) [try this link -- Microsoft Help and Support --], must have cookies turned on, must have Java enabled, must have a late model browser. Restrictions on using other browsers is less of a problem then it used to be. For those that still can’t get in or want to use a more normal search engine there is still Google Usenet Advanced Search.
-- The Euro currency symbol - euro FAQ on the MS KB. takes on greater importance as the euro is now in use. How to obtain fonts and how to key in. The symbol appears as a large C with two cross bars , if you see it here properly you have been updated. WinNT 4.0 SP4 updated my system. On US keyboards (FAQ footnote) use the numeric keypad Alt+0128 (with or w/o num lock). Also see Keying Characters into MS Excel for worksheet entry, VBA entry, and typing; along with a table of characters.
72° 14' 32" -- Displaying Latitude & Longitude, code as time and format the cell as [h]° mm' ss\" under Format|Custom where the degree symbol is typed ALT+0176 on the numeric keypad.
[also see Latitude on xlindex page]
17' 2.4" -- Displaying feet and inches, this example takes feet coded in another cell as 17.2
1' 5.2" -- Displaying feet and inches, this example takes inches coded in another cell as 17.2
Some Links to Frequently Asked Questions relating to Windows 98/NT etc., and some additional EXCEL FAQ sites as well.
- ftp://rtfm.mit.edu/pub/usenet/comp.apps.spreadsheets/faq (text not HTML)
- http://www.faqs.org/faqs/spreadsheets/faq/ <<<<
- MS Knowledge Base articles on apparent errors in addition related to spreadsheets and the use of floating point for all arithmetic and seen when using fractional numbers. Q78113, Q48606
What’s New in Excel (#whatsnew)
- Posting by Jim Rech in misc 2001-04-03
- Voice recognition (in same thread as above) -- Chip Pearson
- Posting By Stephen Bullen, in programming 2001-04-13
- Ⓓ What's New in Excel 2002 (Office XP) (dead-links) ★, Wellesley College, Marge Rowell, also What's New in Excel 2003, Excel 2004 (dead-link)
- Office Online Home Page - Microsoft Office Online">Office XP (replacement link)(dead-link)
- Ⓓ Experience the 2007 Microsoft Office system - Products - Microsoft Office Online (dead-link), Test Microsoft Office free for 60 days trial, and links for more Office and Excel articles.
- Ⓓ Get results with the 2007 Microsoft Office system - Get Started with the 2007 Release - Microsoft Office Online (dead-link)
- Quarantine a file so it won’t open, could affect your personal.xls file.
In the future this page may be split up into two sheets at this point. This will limit the usefulness of using FIND here, but will make the top part look like a more normal “home page”.
If a site is pointed out for a specific topic, it is assumed you will also look at the rest of the site. Also check out the other topics especially those above where you think something would be.
John Walkenbach's site has moved Excel material from j-walk.com to a "php" formatted site, where you will find "VBA Functions" at the bottom of his tips page, now at. (also check the sidebar on his site)The Spreadsheet Page - Excel TipsMicrosoft is notorious for changing url names, such that the half life of most MSDN and other articles. The good news is that the half life has probably been raised to six months. Since the articles are created when referenced, such articles are not archived at archive.org so it makes finding the articles under their new urls very difficult. All Microsoft links were checked during Summer of 2007 and most of them are broken a year later.
You should be able to find old articles at archive.org and then search Google on long phrases in quotes and by including site:spreadsheetpage.com in your Google Search.
I will try to update the links on my page for the MSDN articles but since Microsoft and ISPs intercept 404 errors, they don't even show up as errors if you run a link check scan. Often the best I can do in a short time is to mark it as (deadlink) especially if I can't get a title match that looks like it would be the article. If I spent 10 hours a week fixing Microsoft links, I'd still not be able to keep up.
- Stephen Bullen’s Excel Page. Help with big problems. Definitely a site to bookmark with bookmarks for Excel.
- Excel Blog Headlines, the lastes in Excel blogs listed on John Walkenbach's spreadsheetpage.com
- John Walkenbach (j-walk.com material on new site as spreadsheets.com look for The Spreadsheet Page - Excel Tips [-- http://spreadsheetpage.com/index.php/site/tips --] used to write a Spreadsheets column that you will no longer see in any current issue of PC World (broken link). He also has his own site about spreadsheets [http://spreadsheetpage.com/], a page of Excel Tips and, of course, about the best page of Excel links (Ⓓ dead-link) that you are likely to find, at least until I saw John Raffensperger’s links page (dead link) which used to describe and rate Excel web sites/pages (but site indicates not updated since 17 Dec 2002).
John Raffensperger has his own site and his Excel see The Art of the Spreadsheet and I don't think he maintains an Excel links page anymore.
- Chip Pearson’s Excel Pages includes Hints for New Posters which is posted once a month to microsoft.public.excel.misc and related newsgroups.
- Frank Isaac’s Excel and VBA pages.
- Pivot Table example, additional links to Pivot Tables can be found here.
- Functions for Manipulating Arrays and Ranges, Alan Beban, a downloadable spreadsheet ArrayFunctions.xls contains syntax information and the functions.
- MVPs with Excel sites can be found among several MVPs past & present with Web Sites (mvps.org), if you spend some time in the Excel newsgroups you will recognize many of our names, also read the top of the mvps.org link above for more information about MVPs. A list of current Office MVPs (microsoft.com).
Items here are references to functions, also see the download area.
Also see downloads area, for references such as Peter Noneley's Function Dictionary. I had several of the above listings incorrectly places in that area because of their relationships.
There are many excellent Excel tutorials available on the internet, which accounts for the somewhat excessive number of red (★primary) and blue (★secondary) stars shown below in this area. You should be able to find one that suits your own preferences. Going through one or two tutorials should get you past the introductory materials so that the book you purchase later will be a book you will use as a reference book rather than one simply oriented to beginners.
The ultimate in Excel self-help and finding solutions are the Excel HELP files, and the Microsoft Knowledge Base (MS KB). The following search will provide you with a wealth of various Excel examples.
Microsoft Knowledge Base Search ★
My Search is about: Excel for Windows
Keyword Search using: All Words
Keywords: kbprogramming kbdta kbdtacode KbVBA kbhowto
[x] Full text
[x] Titles and excerpts
Another valuable source of information are the peer-to-peer newsgroups where users post and answer questions, and the Google newsgroup archives where practically every questions and answers to almost any question can be found more information on Newsgroups and the Google archives. When you use text only newsgroups
Forums, most so called forums are recognized by their advertising banners and other revenue generating advertising in a parasitic relationship to the newsgroups where the questions are really answered. The worst part is that such forums (well over 60 Excel forums) is that they interfere with web searches by pulling in copies of newsgroup threads that they touch. Many forums also don't really maintain correct threading leading to confusion. There are a few good forums (perhaps 2 to 4) and you will not find questions or answers posted through them on a web search or on a groups search at least not through Google.
Web based interface to Excel newsgroups, the only one that I would recommend if you must use a web interface due to a corporate firewall is Google Groups. Since Google has web based searches (Google) and Google Groups searches, Google is very careful to exclude their newsgroup database from a web search. [that is no longer true, newsgroups are appearing in web searches, be careful, try to stop the overlap] Another web based interface that does not interfere (in itself) with web searches is Microsoft Communities hosted on Microsoft.
The newsgroups are a great source of help. Most of the macro solutions posted are done in microsoft.public.excel.programming so include that in the newsgroups you look at.
You could pick up books by John Green or John Walkenbach (books), both are frequent posters in the newsgroups and their books are informative. I would go for the later XL2000 books even for XL97, since most of the information will be the same. Other than XL2000 having more web stuff in it XL97 and XL2000 macro language and usage are essentially the same.
A lot of your self help will come from looking at the HELP files for VBA, which you will not find from the help at the Excel spreadsheet, but rather from where you code your macros. In XL95 while on a macro sheet, and in XL97 and above when your are in the VBE (alt + F11). The other place is by recording a macro and looking at the code.
The recorded macros do not do loops and do not combine things together. When you modify recorded macros you want to look to reduce dependency on the activecell. Most things can be done without changing the activecell. You can generally combine statements and eliminate activecell. Look at other macros to see how this might be done. Also look at macros to see how to reduce their focus to a limited selection area by checking for lastcell or by processing only non-empty cells. If you watch for these things in your code and other peoples code that would be a good start.
Forms, some links posted by Harald Staff, misc 2001-04-05
10 Tips to Improve Your Spreadsheet Style by John F. Raffensperger, be sure to see newsgroup discussion. Additional information toolbar addin for auditing(precedents/dependents) Also see John’s educational site: The New Guidelines for Writing Spreadsheets and his links page where he has Spreadsheet Style as one of the topics, and has given ratings on webpage content of all of the referenced links.
The following sites will provide links to a lot of Excel information. The first has links to other sites belonging to other MVPs for Excel, well above average; and the second is a links page on John Walkenbach’s site that is as complete as you will find. (What is the MVP program at microsoft.com)
That’s it. I don’t expect to see a more complete list than John Walkenbach's, you can email him and be added to the list. Though there may be some specialized (noncommercial) lists that relate to a specific profession -- accountants, engineering special topics, statisticians, teachers. For a list of Excel sites that has been evaluated by John Raffenspurger for content, and style see style (don’t ask to be added if your site is “under construction” or does not have content to help Excel users).
- Microsoft MVP, Excel sites (past and present MVPs listed at mvps.org.
- John Walkenbach’s Excel Links (dead-link, none found at spreadsheets.com) Better than your average Excel Link lists. (what's.the.best.excel.site)
- Excel and VBA portions Internet Software Guide for Engineers [broken link, archived] by Ricardo Carvalho. Microsoft Excel 97 and Visual Basic for Applications list of useful resources. Has several links to Excel Tips web pages.
- John Raffensperger has a links page (see Style) rated by content and style but not being updated.
- Collected Lists at Major Portal Sites
- Collections of Excel Links by Classifications
- The Excel Nexus - THE Ultimate Excel Portal (began Mar 2004, Jon Wittwer), links rated by user response.
These Links are frequently cited in newsgroups for places to find Excel code. These were basically pulled from one such posting. Many of these people have been awarded as Microsoft Excel MVP.
- http://www.cpearson.com/excel/MainPage.aspx - Chip Pearson (Excel MVP)
- http://dmcritchie.mvps.org/excel/excel.htm -- David McRitchie (Excel MVP)
- http://www.contextures.com -- Debra Dalgleish (Excel MVP)
- http://spreadsheetpage.com and http://spreadsheetpage.com -- John Walkenbach (Excel MVP)
- http://www.erlandsendata.no/english/index.php?t=enhelp -- Ole P. Erlandsen (Excel MVP) and lots of tips about Excel and VBA programming
- http://www.oaltd.co.uk/Excel/Default.htm -- Stephen Bullen (Excel MVP)
- http://www.appspro.com/Tips/Tips_And_Tricks.htm -- Rob Bovey
- http://www.mvps.org/links.html#excel -- MVP sites (mvps.org, there is lot more there than just Excel here)
Most of the sites above also have downloads so always check out the rest of the site.
Excel to HTML (#xl2html) -- information & downloads
HTML material particularly involving conversion of Excel sheets to HTML has been moved to it’s own page.
More Downloads for Excel -- downloads (#moredown)
- Free Excel Downloads Application Professionals -- (Rob Bovey).
Rob Bovey’s utilities include a code documentation for macros, and “Code Cleaner” (source in document.src), a code cleaning utility for macros (& XL97 Userform) to reduce size (exports & reimports), recovering 30% to 40% reduction if never done before.
- Some additional downloads can be found in the related area of my BuildTOC page. Also see the references and tutorials sections earlier. John Walkenbach’s site and Steve Bullen’s site.
- A reference to definitely download and maintain on your computer – also see additional Excel Tutorials (above)
Excel Function Dictionary ★ by Peter noneley, available as a zipped Excel file, containing actual examples of worksheet functions. This workbook has 157+ sheets, each with an explanation and example of an Excel function.
- Excel Cheatsheet may help you with what’s available or how to start using a feature. (& PDF format) [replaced a bad link, probably not the same]
- List of 339 Worksheet Functions ★ in French, English, Spanish, German, Portuguese (Brazilian), Dutch, Swedish, Italian, Finnish -- on Christian Herbé’s site [ Babelfish French to English], Courtesy of Laurent Longre. [ Babelfish French to English]].
API calls allow you to find such things as Username, Computer name, Operating System, and to such things as Bubble Sorts, etc. Many of Excel sites also have such code and downloads. (also see Properties. I have no API code on my site).
- Microsoft Office 2010 - an Office Watch newsletter, replaces Woody’s Office Watch available in archives, Peter Deegan took over from Woody Leonhard in . (archives at archive.org)
- Excel Experts E-Letter Archives produced by David Hager is available on John Walkenbach’s site. No recent activity but valuable, included on a search of John’s site.
- Mailing Lists work very much like newsgroups. The Excel-L mailing list is where really difficult problems are already solved. and they have their own search. Instructions to subscribe or at Unfortunately for those who just want to search, you must now subscribe(n/c) to these lists in order to view, search, or submit, and is not available on Google.
File Extensions in ExcelSee Excel HELP for more extensions related to Excel and additional information.
Help --> file formats --> converting --> File format converters supplied with Microsoft Excel
XLA MS EXCEL add-in file written in VBA XLB MS EXCEL toolbar file XLC Excel version 4 chart file XLK MS EXCEL backup file XLL MS EXCEL add-in file written in C or C++ (.DLL related) XLM MS EXCEL macro XLS Excel for Windows spreadsheet file XLT MS EXCEL template XLW MS EXCEL saved workspace file
Some others you may encounter and possibly backup.
ACL, Automatic Correction List (in your windows directory)
WAB, Windows Address Book
Be sure that your folder options (My Computer) show file extensions for your own safety. Most people should also make sure they see hidden files as well, in fact you may not see your *.xlb (toolbars) file unless you do show hidden files. System Files may be hidden so people don’t see them, don’t know about them, don’t copy them, or can’t delete them.
How to configure Internet Explorer to open Office documents in the appropriate Office program instead of in Internet Explorer. You may find IE is opening Excel (.xls) files from the internet instead of letting Excel open them.
More File Extensions:Some tables of file extensions for further interest, including one in German.
- List of Extensions on your system. Using Command: assoc
- After Hours File Type Reference Table ★ (links)
- FILExt - The File Extension Source ★ (list with links)
- Webopedia: Data Formats and Their File Extensions ★ (list only)
- Filex, File Extension List, (hit refresh if it doesn’t come up)
- File format, standards, specifications * -- use Case Insensitive when searching. Search provides more information than the listing links at bottom.
- TheReference: Computers: File Extensions
- List of Extensions (untitled) -- archived link
- Paul Oliver's Wotsits Format has potential, great idea, supply the file extension to obtain software manufacturer’s site or relevant information. If you don’t find it you will have to use one of the above links to find the manufacturer then find the information yourself.
- ExtSearch 1784 entries
- Outlook, dangerous extensions, list of file extensions blocked by Outlook as potentially harmful. Many of the current viruses use .pif, .shb and .shs, in addition to the more obvious executables: .exe, .cmd .bas.
- Princeton PC User Group see topic “Extend the Extension” on page 3, 1998-08-10, tells how to use right-click to mean different things depending on the file extension. Also take a look at Do your own Right-click menus.
- Understanding Windows File Associations
- WhatIs.com, list of File associations.
The is another MS KB topic on this page.
The following happen to be VBA -- MS KB articles are mentioned on at the bottom of other articles on this site where appropriated.
Other material on MS site
- Q149502 XL: Resources for Programming with Visual Basic for Applications (articles)
- Q163435 VBA: Programming Resources for Visual Basic for Applications (mainly publications)
- Office 97/Visual Basic Programmer’s Guide -- Microsoft archives, online copy of ISBN:1-57231-340-4.
This/These looked just too good to ignore.
- James A. Eshelman, Supporting Microsoft Desktop Systems, very compact arrangement. MS MVP, Desktop Systems
A lot of my material is REXX macros as you might discover if you leave the Excel area. About the only path back is the default entry for my site, the back key on your browser, or a bookmark.
One thing that bothers me is the black background for the DOS window under Windows NT. I made some changes for myself, I don't know if it would work for you or under Windows 95, but it works well for me. Refer only to the section of the document marked Properties for DOS Session under Windows NT, my own usage and use you browser's BACK key to return here. Tried to do this on a Win98 system and properties did not have a Color tab, so it is probably only available on WinNT
If you don’t like your web browser’s default colors (gray background) for viewing .TXT documents you might take a look at another document of mine. Be sure to record your original settings, changed settings, and the date so you can put them back if you don’t like your changes.Microsoft Windows Vista Home Basic Edition, some notes on conversion from Windows 2000, see Vista (2007-08-10).
Web Pages – Table of Contents for Web Pages (#webpage_toc)
Firefox users can see a Table of Contents at anytime for pages with H1-Hn headings by installing an extension for sidebar viewing, or a bookmarklet to create a secondary statusbar dropdown list. (see Document Map)
Open Directory Project: http://dmoz.org/Computers/Software/Spreadsheets/Excel/
- Registered to Microsoft Corporation.
Important Note; This site has been moved to mvps.org where several current and former Microsoft MVPs have pages and many at least have links, you should now be reading this page at http://dmcritchie.mvps.org/excel/excel.htm without ads and about four times faster than was available formerly at the Geocities site (Geocities shut down free websites Oct 26, 2009) where traffic was not only slow but the site was frequently unavailable.
Excel questions not directly concerning my web pages are best directed to newsgroups
such as news://msnews.microsoft.com/microsoft.public.excel.misc where Excel users all around the clock from at least 6 continents ask and answer Excel questions. Posting suggestions and netiquette. More information on newsgroups and searching newsgroups. Google Groups (Usenet) Advanced Search Excel newsgroups (or search any newsgroup).
Non Excel web search sites and information, basically what one might need away from their own computer when travelling -- but it does include a Search of “My Excel Pages” at the top, and some quick links at the very top.
Missing/incorrect links for pages of other people and Microsoft, see Missing Webpages, Missing sites on Referenced pages. After spending a little over one year trying to work with the horrible Excel 2007 ribbon interface, I basically threw in the towel on Oct 1st, 2008 after not being renewed in the MVP Program. I spend my free time now playing with the Firefox web browser. Fortunately my Excel web pages apply to Excel regardless of version, and it is so nice to hear that they still help a lot of users.
Each of my web pages has it's own email introduction, so I can tell what page you were looking at when you email me, so please use the email link on the page you are writing about.
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2010, F. David McRitchie, All Rights Reserved