Home > Excel Help Portal > Formatting Excel

<<  (Page 7)  Previous              Next  (Page 2)  >>

Formatting Excel

The ExcelUser portal offers links to help pages created by top
Excel web sites. This section discusses ways to format Microsoft
Excel.

1. Build Table of Contents, similar listings, working with Hyperlinks Coding The coding seen or described on this page can be cut and pasted directly from the following locations: Coding for macros BuildTOC, SortAllSheets, DelHyperLinks, MakeHyperLinks, RunSubFromActiveCell, LinkFix, LinkFix_GoTo, MakeHTML_Link and the user defined functions URL and MSKBQ can be... Keywords: Add-In, Charts, AutoCorrect, Sorting, Export, OLE, AutoFormat, Colors, Custom Number Formats, Fonts, Outline, Styles, Arrays, Errors, Formulas, Links, Printing, Class, Clipboard, Command Button, VBA, Macro, Object, User Defined Function, VBE, Files, Toolbar, HTML, Hyperlinks, Internet Explorer, Web, Comments, Window, MID, INDIRECT, VLOOKUP, OFFSET, CELL, MATCH, INDEX

 
2. Shortcut Keys in Excel 2000 (Excel Version 9) Create your table of builtin shortcut keys as follows: HELP -- Index -- Shortcut keys -- built-in keyboard shortcuts copy and paste each category into a ShortCutKeys spreadsheet Put topics in Red 12 point bold font, the rest Black 10 point Normal (all Arial) Alternatives to shortcut keys: Toolbar... Keywords: Add-In, Charts, SERIES, AutoShapes, AutoFilter, Sorting, Borders, Colors, Fonts, Format, Outline, Styles, Symbol, Arrays, Errors, Formulas, Links, Speech, Defined Name, PivotChart, PivotTables, Printing, Clipboard, Command Button, VBA, Macro, Object, Error Message, Files, Macintosh , Menu Command, Toolbar, HTML, Hyperlinks, Internet Explorer, Web, Cell Note, Comments, Window, FIND, RIGHT, SIGN, LEFT

 
3. How to use Visual Basic for Applications examples to control UserForms in Microsoft Excel Explains how to use ActiveX controls in UserForms in Excel. Examples of Visual Basic for Applications code are... KB#829070. Keywords: Query, Days, Colors, Fonts, Format, Styles, Arrays, Errors, Password, Startup, Defined Name, Combo Box, Command Button, VBA, Macro, Object, Spin Button, UserForm, Files, Web, Validation, Window

 
4. Event Macros, Worksheet Events and Workbook Events Excel 2002 includes format as a change Event so possibly this should change the colorindex at the end and turn Event off/on around it. But I don't have Excel 2002.... Keywords: Criteria, Sorting, Dates, Days, Borders, Colors, ColorIndex, Conditional Formating, Fonts, Gridlines, Styles, Arrays, Errors, Formulas, Links, Printing, VBA, Macro, Object, VBE, CD-ROM, Error Message, Files, Toolbar, HTML, Hyperlinks, Internet Explorer, Web, Comments, Validation, Window, MIN, NOW, TIME, ROW, SUBTOTAL, MATCH, COUNTIF, PROPER, OFFSET

 
5. Onsite pages and related off-site material for Excel pages Keywords: Add-In, Analysis ToolPak, Charts, SERIES, Graphic, AutoFilter, Criteria, Query, Rank, Sorting, Wildcard, Dates, Days, Months, Years, Download, API, DLL, Export, OLE, Statistics, Borders, Colors, Custom Number Formats, Fonts, Gridlines, Patterns, Styles, Symbol, Arrays, Errors, Formulas, Links, Sounds, Startup, PivotTables, Printing, Class, Clipboard, Macro, VBA, VBE, CD-ROM, Corrupted, Files, Registry, Templates, Toolbar, Text Files, HTML, Hyperlinks, Internet Explorer, Web, Auditing, Column Width, Comments, Validation, Window, FIND, COUNTA, COUNTIF, DATEDIF, OFFSET, INDIRECT, MAX, HLOOKUP, VLOOKUP, PROPER, REPT, INFO, CELL, NOW, RAND, TODAY

 
6. Show FORMULA or FORMAT of another cell Not satisfied with the methods of showing formulas in Excel: (#getformula) Tools -- Options -- View -- (formula on/off) Ctrl+` is the equivalent shortcut (toggle on/off) -- accent grave to left of the 1,2,3 on the top row Also see Select cells with formulas or constants which you can tab through or... Keywords: Add-In, Criteria, Sorting, Days, Download, API, Statistics, Borders, Colors, Custom Number Formats, Fonts, Outline, Patterns, Styles, Symbol, Arrays, Errors, Formulas, Links, Printing, Class, VBA, Macro, Object, UDF, User Defined Function, Files, Toolbar, HTML, Hyperlinks, Web, Auditing, Comments, Validation, Window, NOW, VALUE, DATEDIF, REPT, COUNTA, COUNTIF, OFFSET, LARGE, INDEX, BAHTTEXT, LEN, CODE, TRIM, MOD, CHAR

 
7. Color Palette and the 56 Excel ColorIndex Colors This page contains some VBA macros and User Defined Functions. If you need assistance to installing eitherplease refer to Install a Macro or User Defined Function on my Formula page. Scope of the Color Palette: Each workbook has it's own palette. To change the default, change your book.xlt template... Keywords: Add-In, Analysis ToolPak, Charts, Graphic, Text Box, Sorting, Dates, Borders, Colors, ColorIndex, Fonts, Format, Gridlines, Patterns, Styles, Errors, Formulas, Links, Printing, VBA, Macro, UDF, User Defined Function, VBE, Smart Tag, Templates, Toolbar, HTML, Hyperlinks, Internet Explorer, Web, Comments, Window, COUNTIF, RIGHT

 
8. Proper, and other Text changes -- Use of Special Cells If you want the contents of formulas to also be converted use Bill Manville's code or a variation. (see related below) This page contains some VBA macros. If you need assistance to install or to use a macro please refer to my «Getting Started with Macros« or delve into it deeper on my Install page.... Keywords: AutoCorrect, Sorting, Colors, Fonts, Format, Symbol, Errors, Formulas, Printing, VBA, Macro, Object, Files, Toolbar, HTML, Web, Comments, LOWER, PROPER, FIND, UPPER, TRIM, SUBSTITUTE

 
9. Mail Merge, Printing Labels using Mail Merge with data from Excel Excel can be used to supply the data to mailmerge in MS Word. I found this a little tricky first time but have done this from both XL95 and XL2000. Excel makes a nice table column for name, address, city, zip state, zip code. Excel does not give you reformatting options to create another sheet... Keywords: Query, Sorting, Months, Download, DDE, SQL, Fonts, Format, Patterns, Styles, Symbol, Errors, Formulas, Links, Printing, Macro, Object, VBA, VBE, Files, Templates, HTML, Hyperlinks, Web, Comments, Window, TRIM, MATCH

 
10. Conditional Formatting was introduced with Excel 97 and is a terrific feature, but there is a limit of 3 conditional sets per cell (like 3 wishes). Conditional Formatting, while in effect for a cell, will override the text colors that can be produced for numeric values by normal cell formatting. Filters, Validation, and... Keywords: AutoFilter, Criteria, Rank, Sorting, Dates, Days, Holidays, Download, Borders, Colors, Conditional Formating, Fonts, Gridlines, Patterns, Arrays, Errors, Formulas, Links, PivotTables, VBA, Macro, UDF, User Defined Function, VBE, Error Message, Toolbar, HTML, Web, Comments, TODAY, COUNTIF, MOD, SUBTOTAL, COUNTA, MATCH, FIND, WEEKDAY, EXACT, LARGE, TRIM, CELL, MAX, VALUE, OFFSET

 
11. Excel to HTML conversions The code referred to on this page are VBA macros. If you need assistance to install macros please refer to Getting Started with Macros or for even more information for more depth after using installing/using some macros see Install a Macro or User Defined Function on my Formula page. The generated... Keywords: Add-In, Charts, Query, Sorting, Download, Export, XML, Borders, Colors, Fonts, Format, Styles, Formulas, Links, Printing, Clipboard, VBA, Macro, Object, User Defined Function, Files, HTML, Hyperlinks, Internet Explorer, Web, Comments, Window, ROW

 
12. Delete Cells/Rows in Range, based on empty cells, or cells with specific values Assistance to Install a Macro or User Defined Function on my Formula page. Delete Empty Cells but don't disturb rows The DEL95HTMLemptyCells is designed to clean up the mess that Excel 95 creates when pasting from a web page into Excel. My experience with pasting Excel 95 was that instead of... Keywords: Add-In, SERIES, Sorting, Colors, ColorIndex, Format, Errors, Formulas, VBA, Macro, Object, User Defined Function, HTML, Web, Comments, Discontiguous, TRIM, FIND, ROW

 
13. Shortcut Keys in XL95 (Excel Version 7) Rather than just telling you to create a Shortcut keys sheet in an Excel file I decided to show you what mine looks like. You should create your own, of course. Creation of Shortcut keys spreadsheet Shortcuts are entirely optional. In XL95 a shortcut key can be assigned to a macro using: Tools --... Keywords: Add-In, Charts, SERIES, AutoFilter, Borders, Format, Outline, Styles, Arrays, Formulas, PivotTables, Printing, Command Button, VBA, Macro, Object, Macintosh , Menu Command, Toolbar, Web, Cell Note, Comments, Window, FIND, SIGN, LEFT, RIGHT

 
14. How to use Addresses from an Excel 2002 Worksheet to Create Labels in Word 2002 You can use the Mail Merge feature in Word to create and print labels for a mass mailing by using data from an Excel Worksheet. This article contains instructions that you can use to create such a list. When you use the Word 2002 mail merge... KB#318117. Keywords: Criteria, Query, Sorting, Microsoft Access, Sales, Fonts, Format, Symbol, Errors, Printing, Toolbar, Web, Validation, Window

 
15. How to create a Form Letter by Using Information from an Excel 2002 Worksheet in Word 2002 This article explains how to use the Mail Merge feature in Microsoft Word to create and print form letters by using data from a Microsoft Excel worksheet. When you use the Word 2002 Mail Merge feature, Word merges a main document with a recipient... KB#318118. Keywords: Criteria, Query, Sorting, Microsoft Access, Third-Party, Sales, Fonts, Format, Symbol, Errors, Printing, Templates, Toolbar, Web, Validation, Window

 
16. Cell Comments XL2HTMLX (extended) version which adds the grey shaded Column and Row headers. IExplorer is then invoked for a review and the example (coding) can be copied from view source into a permanent web page. As the actual purpose is to generate as little code as possible you will have to right justify any... Keywords: AutoShapes, Graphic, Text Box, Borders, Colors, Fonts, Format, Outline, Errors, Formulas, Links, Printing, VBA, Macro, Object, VBE, Setup, HTML, Internet Explorer, Web, Cell Note, Comments, Validation, Window

 
17. How to use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET This article discusses how you can use ADO.NET to retrieve data from a Microsoft Excel workbook, modify data in an existing workbook, or add data to a new workbook. To access Excel workbooks with ADO.NET, you can use the Jet OLE DB provider;... KB#316934. Keywords: Data Table, Query, Export, Microsoft Access, OLE, Sales, Fonts, Format, Styles, Errors, Formulas, Defined Name, VBA, Object, File Name, Window, DB

 
18. Guidelines for Selecting the Appropriate Picture Format This article discusses the following topics: The various picture file formats that you can insert into Microsoft Office programs How to select the best format for a particular purpose How to select the appropriate resolution and color depth for... KB#320314. Keywords: Third-Party, Ratio, Colors, Format, Arrays, Formulas, Printing, Files, Web

 
19. Guidelines for Selecting the Appropriate Picture Format This article discusses the following topics: The various picture file formats that you can insert into Microsoft Office 2000 programs How to select the best format for a particular purpose How to select the appropriate picture resolution and... KB#272399. Keywords: Graphic, Third-Party, Ratio, Colors, Format, Arrays, Formulas, Printing, Files, Web

 
20. Summarizing Data Examples (an Overview) Conditional Format: =MOD(SUBTOTAL(3,$A$1:$A1),3)=0Shows color banding working with/without Filtering, but it does require that there be something in Column A for every row. More on Color Banding on my Conditional Formatting page. Filters, Validation, and Conditional Formatting are very similar to... Keywords: Charts, Advanced Filter, AutoFilter, Criteria, Rank, Days, Download, Borders, Colors, Conditional Formating, Fonts, Gridlines, Outline, Arrays, Errors, Formulas, Links, PivotTables, VBA, Macro, UDF, Web, Comments, MOD, COUNTIF, SUBTOTAL, MATCH, VLOOKUP, ROWS, COUNTA

 
21. Stocks, Quotes and keeping track of Keywords: Add-In, Charts, Query, Dates, Days, Years, Download, DDE, Export, ROI, Sales, Statistics, Colors, Format, Links, Password, Macro, VBA, Files, Templates, Text Files, HTML, Hyperlinks, Web, Comments

 
22. SYMBOLS for HTML and Excel Use Font Tables as Rendered by your browser (fonts.htm -- CODE, Hex, CHAR, bold, symbol, webdings, wingdings, wingdings 2, wingdings 3) Unicode (new page), and Unicode through 16000 as a zipfile. See newsgroup thread SYMBOLS for HTML and Excel use (this page) Excel characters as seen in the US... Keywords: Add-In, Charts, AutoCorrect, Sorting, Colors, Fonts, Format, Symbol, Formulas, Printing, VBA, Macro, Toolbar, HTML, Links, Web, Comments, CODE

 
23. Bookmarklets (JavaScript Favelets) 0  James Highlight Links, variations most Yellow IE only, one in garish green. At least two toggle color, the one selected earlier for Mozilla works in both but it resets rather than retaining font color. Count strings, images, fails in IE Scroll Position, Show document size, Titles, all fail in IE... Keywords: Sorting, Borders, Colors, ColorIndex, Fonts, Format, Styles, Arrays, Links, Startup, Toolbar, HTML, Internet Explorer, Web, Comments, Validation, Window, TEXT

 
24. How to transform a DataSet to spreadsheet XML for Excel by using Visual Basic .NET and ASP.NET This step-by-step article describes how to transform a DataSet to Spreadsheet XML that can be rendered in Excel. The Excel Spreadsheet XML format supports element tags and attributes for Excel functionality such as multi-sheet workbooks, formulas,... KB#319180. Keywords: DLL, SQL, XML, Format, Styles, Errors, Formulas, Password, Class, VBA, Object, Files, Templates, HTML, Internet Explorer, Web

 
25. Excel FAQ - Excel Application and Files When I start XL, I get the error message "Compile error in Hidden Module" An add-in with a programming error is causing problems. Got the error? Uncheck the one you last checked. If no luck, see if there's an .xla file in the startup directory (see previous tip). Scanner software typically place... Keywords: Add-In, AutoFilter, Dates, Colors, Fonts, Format, Errors, Formulas, Links, Sounds, Startup, VBA, Macro, VBE, AutoSave, Error Message, Files, Toolbar, Hyperlinks, Web, Window

 
26. Getting Started with Macros and User Defined Functions If these instructions do not help then try Visual Basic for Applications 101 (Excel) «... Keywords: Add-In, SERIES, Days, Colors, Format, Patterns, Errors, Formulas, Sounds, Startup, Macro, UDF, VBA, VBE, Files, Toolbar, Web, Comments, Window, FIND

 
27. Logos and Graphics into headers/footers Excel does not provide for including graphics directly into the headers and footers. You have a couple of alternatives though. Use a font character (though of limited use works in both headings and footings). Dedicate first row to contain logos, and place the column headings in the second row... Keywords: Charts, Graphic, Borders, Colors, Fonts, Format, Gridlines, Outline, Patterns, Formulas, Page Setup, Printing, Clipboard, Macro, Object, Files, HTML, Web, Comments

 
28. How to Identify and Remove PLDT/CAR/SGV Macro Viruses In November 1997, Microsoft identified a new macro virus, the PLDT macro virus, that infects workbooks in Microsoft Excel for Windows. This macro virus is also known as PLDT97 or Laroux E. In April 1998, Microsoft identified another new macro... KB#176807. Keywords: Add-In, Export, Third-Party, Format, Styles, Macro, VBA, Files, Web, Window

 
29. Menu Options Tutorial Inserting Automatic Page Breaks You can use the subtotal feature to add a page break after each change in a sort key. Use View Manager to Print Summary and Detail Reports from Same Worksheet To print both summary and detail views of the same worksheet you can use Excel's automatic subtotals and... Keywords: Charts, SERIES, Camera, Criteria, Sorting, Sales, AutoFormat, Gridlines, Outline, Formulas, Printing, Macro, Object, Toolbar, Links, Auditing, Cell Note, Window

 
30. How to Use Visual Basic or ASP to Create an XML Spreadsheet for Excel 2002 Excel 2002 supports an XML format with which it can both load and save workbooks (or XML spreadsheets). By using this XML spreadsheet format, you can create multi-sheet, formatted workbooks to Excel without using Automation. This approach may... KB#285891. Keywords: Download, ActiveX, XML, Borders, Colors, Format, Gridlines, Styles, Errors, Formulas, Printing, Object, VBA, File Name, Files, Templates, Internet Explorer, Web, Column Width, Window

 
31. Merge and Unmerge Cells This page contains some VBA macros. If you need assistance to install or to use a macro please refer to my GetStarted page for simpler instructions, or to my GetFormula page for more extensive information on macros. The icon Merge Across is similar to the macro MergeRxR, but MergeRxR will work with... Keywords: Sorting, Borders, Colors, Format, Errors, Formulas, VBA, Macro, User Defined Function, Setup, Web, Comments

 
32. Excel 2000 SR1 Macro Modification Security Update Microsoft has re-released an update to Microsoft Excel 2000 SR-1. This update addresses a vulnerability that might allow malicious code to run in an Excel file without warning. Under typical circumstances, you will see a warning in Excel 2000 when... KB#306604. Keywords: Charts, Download, AutoFormat, Colors, Outline, Errors, Formulas, Password, PivotTables, Printing, Macro, Object, VBA, CD-ROM, Error Message, Files, HTML, Internet Explorer, Links, Web, Window

 
33. Recording and customizing a macro This is the 19th weekly tip at www.MrExcel.com. Many of the tips involve some sort of macro trick. This week, for the Excel users who have never written a macro, I offer a primer on how to record and then customize a useful Excel macro. This simple problem will be used to illustrate how to record,... Keywords: Sorting, Format, Errors, VBA, Macro, Object, Toolbar, Web

 
34. How to Select a Compatible Printer Driver If you want to troubleshoot a problem with your printer driver, try printing with a different printer driver. If you determine that the problem does not occur when you use a different driver, you may need to update or reinstall the original... KB#177512. Keywords: Graphic, Colors, Errors, Printing

 
35. How to Create a Gantt Chart Using Hours as the Scale You can use a Gantt Chart to show the progress of a project or the relationship among the tasks in a project in relation to time. The unit of time that is most frequently used is the day unit. However, you can also use the hour as the unit of time... KB#152820. Keywords: Charts, GANTT, Legend, SERIES, Days, Borders, Format, Patterns, Formulas, Toolbar

 
36. Font Also see Changing Font etc in cell comments Menus (place holder) sheet tabs (place holder) Right-click on your desktop and choose PropertiesDisplayAdvancedActive Title Bar*set the font size to suit your needs*OKOKOK. Go into Control Panel, Display, Appearance and enlarge the size of the scrollbars.... Keywords: Download, API, Colors, Fonts, Format, Styles, Formulas, Links, Printing, VBA, Macro, UDF, User Defined Function, Files, Templates, HTML, Web, Comments

 
37. How to Programmatically Measure the Visible Range in Points You may want to measure the exact range of a Microsoft Excel worksheet in points. This information can make it easier to fill the visible range or a worksheet with a graphic object, such as a chart. This article describes a sample Microsoft... KB#213323. Keywords: Charts, Graphic, Borders, Fonts, VBA, Macro, Object, Web, Column Width, Window

 
38. How to Use the TabStrip Control on a UserForm This article explains how to use the TabStrip control on a UserForm, and provides Microsoft Visual Basic for Applications sample code for this... KB#213254. Keywords: Borders, Colors, Command Button, VBA, Macro, Object, UserForm, Web, Window

 
39. How to Use Macros to Set Row Height and Column Width Microsoft Excel uses the font assigned in the Normal style as the basis for column widths. There is no direct way to assign exact column widths in inches or centimeters without trial and error. Excel bases its measurement of column widths on... KB#213422. Keywords: Fonts, Format, Styles, Errors, VBA, Web, Column Width

 
40. Excel FAQ - Functions and Formats =ROUND(A1/5,0)*5 =ROUND(A1/0.25,0)*0.25 =VLOOKUP(A2,Sheet2!A2:B100,2,FALSE) =SUM(LARGE(A:A,{1,2,3,4,5})) Enter the items down A1:A100. In B1:B100 enter formula =RAND(). Sort the list by B column; top 20 rows is your selection. Press F9 for new B numbers and repeat for a new selection. If the cells... Keywords: Add-In, Analysis ToolPak, AutoFilter, Criteria, Sorting, Fonts, Format, Outline, Formulas, Links, VBA, Validation, ROUND, VLOOKUP, RAND, SUBTOTAL, CELL, MID, LINEST, INTERCEPT, LOGEST, FORECAST

 
41. How to automate Microsoft Excel from Visual Basic .NET This article demonstrates how to create an Automation client for Microsoft Excel by using Microsoft Visual Basic... KB#301982. Keywords: Charts, SERIES, Download, Sales, Borders, Colors, Fonts, Format, Styles, Arrays, Formulas, VBA, Object, Web, Window, RAND, COLUMN

 
42. How to Use the TabStrip Control in a UserForm This article explains how to use the TabStrip control in a UserForm and provides a Microsoft Visual Basic for Applications... KB#155009. Keywords: Borders, Colors, Command Button, VBA, Macro, Object, UserForm, Web, Window

 
43. How to use the Chart Wizard in Excel 2000 This step-by-step article explains how to use the Chart Wizard in Microsoft Excel 2000 to create a chart. Create a Chart To create a chart, you must first enter the data for the chart on an Excel worksheet. Select that data, and then use the... KB#304421. Keywords: Bar Chart, Charts, Legend, SERIES, Data Table, Format, Gridlines, Object

 
44. How to identify and Remove PLDT, CAR, and SGV Macro Viruses in Excel 2000 This step-by-step article shows you how to identify and remove PLDT, CAR, and SGV macro viruses in Excel 2000. In November 1997, Microsoft identified a new macro virus, the PLDT macro virus, that infects workbooks in Microsoft Excel for Windows.... KB#213512. Keywords: Export, Third-Party, Format, Styles, Macro, VBA, File Name, Files, Web, Window

 
45. How to use Natural Language Formulas in Excel 2000 This step-by-step article shows you how to use natural language formulas in Excel 2000. Natural language formulas are a method of referring to a cell range in a table by using row or column labels as the reference name. Natural language formulas... KB#279412. Keywords: Sales, Format, Arrays, Errors, Formulas, Defined Name, Natural language, Toolbar

 
46. Information about how to extract Office file formats and schemas Describes several methods for extracting file format or schema information for Microsoft Excel, Microsoft PowerPoint, or Microsoft Word. If these methods are not satisfactory, a license program is also... KB#840817. Keywords: Download, API, XML, Format, Printing, Files, Text Files, HTML, Web

 
47. How to Create Web Query (.iqy) Files This article explains how to create Web query (.iqy) files for use with Microsoft Excel... KB#157482. Keywords: Query, Format, Symbol, Files, Text Files, Web

 
48. Excel Statistical Functions: Representing Ties by Using RANK This article discusses how to use RANK to give an average rank value to tied... KB#828678. Keywords: Rank, Format, Arrays

 
49. How to Automate Microsoft Excel from Visual Basic This article demonstrates how to create and manipulate Excel by using Automation from Visual... KB#219151. Keywords: Charts, Sales, Borders, Colors, Fonts, Format, Arrays, Errors, Formulas, VBA, Object, Window, RAND, COLUMN

 
50. How to Create/Format an Excel Workbook Using Visual J++ Automation This article describes Automating Excel 97 or later from a Visual J++ client using the Java programming language. The project uses the Excel type library, and illustrates object oriented... KB#219430. Keywords: Charts, DLL, Sales, Borders, Fonts, Format, Arrays, Errors, Formulas, Class, Object, VBA, Window, RAND

 

<< (Page 7)  Previous              Next  (Page 2) >>