Home > Excel Help Portal > Formatting Excel

<<  (Page 4)  Previous              Next  (Page 6)  >>

Formatting Excel

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

201. Excel Saves XML Files in the XML Spreadsheet Format When you save a file in the Extensible Markup Language (XML Spreadsheet) format, the file is not saved in a generic XML file format. -and- XML Spreadsheet (*.xml) is the only XML format listed in the Save As dialog... KB#287734. Keywords: XML, Format, Styles, VBA, Files, Save A File, Web

 
202. Working with fractions Some types of data, such as stock market quotes, normally display as fractions, not decimals. To enter a fraction in Excel, type the whole number (or integer) followed by a space, and then type the fraction, using a slash (for example, 5/8). If you type only a fraction, Excel may interpret it as a... Keywords: Analysis ToolPak, Charts, Format, Formulas, Printing, DOLLARFR, DOLLARDE

 
203. Records Needed to Make a BIFF5 File Microsoft Excel Can Use This article documents which records are required in order to build a valid BIFF5 file that Microsoft Excel will... KB#147732. Keywords: OLE, Fonts, Styles, Files, INDEX

 
204. Update charts automatically when you enter new data Q. I use an Excel workbook to track and chart daily sales. Since the number of data points changes every day, I have to update the chart manually so it includes the new data. How do I get the chart range to expand automatically? When you select a chart series in Excel 97 or later, the ranges used... Keywords: Charts, SERIES, Sales, Format, Outline, Formulas, Printing, OFFSET, COUNTA

 
205. Frequently Asked Questions About the Find and Replace Feature in Excel 2002 In Microsoft Excel 2002, the Find and Replace dialog boxes of earlier versions have been replaced by a single dialog box. The default tab selection depends on which of the two commands is used to open the dialog box. You can switch between finding... KB#293450. Keywords: Format, Errors, Error Message

 
206. How to change the Color Palette for Workbooks in Excel This step-by-step article explains how to change the color palette so that you can use custom colors for elements of the workbooks in Microsoft Excel. You can modify colors for many workbook elements, including the following: Worksheet Tabs... KB#288412. Keywords: Charts, Colors

 
207. Creating a Custom Chart Type from the Edit menu, or right click on the chart and select Chart Type... from the pop up menu. Click on the Custom Types tab, and choose the User-Defined option below the list of chart types. Click the Add button, then fill in a name and description for your chart. After you press OK, notice that... Keywords: Charts, Fonts, Format

 
208. Restrict cursor movement to unprotected cells Q. The formulas in my worksheet use values in several input cells. I've unlocked the input cells and protected the sheet so the user can't change the formulas. Can I set things up so the cell cursor moves only to the input cells? Yes. You've already unlocked your input cells and ensured that all... Keywords: Charts, Format, Formulas, Password, Printing, VBA, Macro, Toolbar

 
209. How to Suppress Error Values on a Worksheet In Microsoft Excel, you can prevent error values (such as #N/A, #VALUE!, #REF!, and #NUM!) from being... KB#87299. Keywords: Colors, Custom Number Formats, Fonts, Errors, Formulas, Printing

 
210. Identify formulas using Conditional Formatting How many times have you accidentally deleted or overwritten cells containing formulas only to discover the mistake after it's too late? One solution is to write-protect important cells. Another approach is to give those cells a visual flag. This clever technique was submitted by David Hager. It... Keywords: Charts, Format, Formulas, Printing, Macro, INDIRECT

 
211. Jon Peltier\s Chart Tutorials This is a set of tutorials about charting in Microsoft Excel. The first tutorial is the most basic, an introduction to making charts. Follow up tutorials deal with formatting parts of the chart, updating charts with additional or changing data, advanced techniques and tricks, and automating charts... Keywords: Charts, Legend, SERIES, Data Table, Format, Gridlines, Outline, Errors, Formulas, Object, VBA, Web

 
212. Borders of AutoShape on Embedded Excel Chart Change Thickness When Printed When you print an Excel chart that is embedded in another document, AutoShape borders appear thicker than they do if you print the same chart from... KB#307609. Keywords: Charts, Legend, AutoShapes, Dates, Borders, Printing, File Name, Files

 
213. Color Rows by Criteria This piece of re-usable code is called by any of the routines and already “knows” what the ColorCode index number... Keywords: Charts, Criteria, Borders, Colors, Formulas, Links, VBA

 
214. How to Center Numbers and Have Decimals Line Up in Excel In Microsoft Excel, you can center your data in a column on a worksheet so that the decimals appear to line... KB#44737. Keywords: Custom Number Formats, Column Width

 
215. Comparing Two Lists With Conditional Formatting Excel's Conditional Formatting feature has many uses. Suppose you need to compare two lists, and identify the items that are different. The figure below shows an example. These lists happen to contain text, but this technique also works with numeric data. The first list is in A2:B19, and this range... Keywords: Charts, Colors, Format, Formulas, Printing, COUNTIF

 
216. Creating a thermometer style chart Most people are familiar with "thermometer"-style graphs, which show the percentage of a project completed. It's easy to make such a display in Excel. The key is to create a chart that uses a single cell (containing a percentage value) as a data series. The example below tracks daily progress... Keywords: Charts, Legend, Plot, SERIES, Format, Patterns, Styles, Formulas, Printing

 
217. Lines in a Graph Are Missing When Inserted into an Office Document When you insert a graph into one of the programs listed in the Applies to section of this article, some of the lines in the graph are... KB#249079. Keywords: Colors, Format

 
218. Font Size and Displayed Rows and Columns May Change When You Edit Embedded Excel Object in PowerPoint If you are working in a PowerPoint presentation that contains a Microsoft Excel worksheet object, the number of rows and columns displayed and the size of the fonts may change when you edit the... KB#252382. Keywords: Fonts, Object

 
219. How to change the Font in a Comment in Excel 2000 This article explains how to change the font (style, size, and color) for the text of a comment in Microsoft Excel. To add a comment to the active cell, click Comment on the Insert menu, and then type your text in the yellow comment box. How to... KB#211883. Keywords: Fonts, Format

 
220. Copying just Subtotals to a new Range Peter from Cincinnati offers this weeks tip: Use the Goto - Special command to copy just the subtotals to a new area. You can use the Data - Subtotals command to automatically subtotal a range of data as shown above left. The solution lies in the obscure but powerful GoTo Special functions. Here... Keywords: Outline, VBA, Web

 
221. How to Find the Percentage of Change Between Values In Microsoft Excel, there is no built-in function to calculate the percentage of change from one value to another. However, you can use the following formula to calculate the percentage change ( new_value - original_value )/ABS(original_value)... KB#214078. Keywords: Sales, Format, Formulas

 
222. Excel Code Examples I have this massive application that extracts our department telephone list each month and massages it into a number of different formats. There are reports of home phones that go to everyone in the division, reports of addresses that go to key personnel, pivot tables showing building and... Keywords: Charts, Criteria, Borders, Colors, VBA

 
223. How to use Only Selected Parts of AutoFormat in Excel 2000 This step-by-step article describes how to use only selected parts of an AutoFormat. You can also find this information in more detail in Microsoft Excel 2000 Help. For more information, please see the REFERENCES section later in this article.... KB#263557. Keywords: AutoFormat

 
224. Creating Combination Charts A combination chart is a chart that combines two or more chart types in a single chart -- for example, a chart that has a line series and a column series. When you choose the Custom Types tab in the first step of the Chart Wizard, you'll find the following combination charts listed: Many users... Keywords: Charts, SERIES, Format, Formulas, Printing

 
225. Graduated Symbol Is the Only Symbol Available in Microsoft Map When you create a Microsoft Map object in Microsoft Excel, the only symbol that is available in the Microsoft Map Control dialog box is the Graduated symbol (five-pointed... KB#214387. Keywords: Microsoft Map, Format, Symbol, Object

 
226. Excel - Protecting cells You may choose to protect the contents of a cell in order to prevent accidental deletion or to stop someone else from wrecking the integrity of your calculations. (see spreadsheet testing). If you have a complex formula which has been properly tested it may be advisable to prevent further changes... Keywords: Format, Errors, Formulas, Links, Password, Validation

 
227. Color Font of Cells if Unlocked My customer had a number of workbooks, each having up to 20 worksheets in each one. He was trying to unlock some cells and lock down the headings and formula cells. The good thing about this macro is that it can be run multiple times against the same sheets so if you are constantly changing and... Keywords: Colors, Fonts, Formulas, Password, VBA, Macro, IS

 
228. Counting cells by interior color using VBA if only it were that easy. You need to have a User Defined Function in VBA. Below is the code for the function. You need to enter this in a new code module in the workbook. If you are unfamiliar with VBA, read this page.Once you have the code entered, you can use this... Keywords: Colors, Format, User Defined Function, VBA, Web

 
229. Conditional Formatting: Considering the '4th' option Select your range of cells.Select conditional formatting from the Format menu.From the drop down menu, set condition 1 to “Cell Value Is” and “Less than or Equal to” and put in the value -25. Now press the format button and choose red from the patterns tab and click Okay.Click add, and repeat the... Keywords: Colors, Format, Patterns, VBA, Web

 
230. Download free Autoshapes Drawing Tools After downloading and installing this application from the Microsoft web site, you will be able to easily add cool symbols of any size and color to Excel, Word and other Office applications. In Excel, select Insert, Picture, Autoshapes from the menu. You will be presented with an Autoshape toolbar... Keywords: AutoShapes, Download, Colors, Format, VBA, Toolbar, Web

 
231. How to Change the Font in a Comment This article explains how to change the font (style, size, and color) for the text of a comment in Microsoft Excel... KB#166254. Keywords: Colors, Fonts, Format

 
232. How to hide Gridlines in a Selected Area of a Worksheet in Excel 2000 This article describes how to hide the gridlines in a selected area of a worksheet in Excel 2000. Hide Gridlines To hide gridlines in a selected area of a worksheet, follow these steps: Select the range of cells for which you want to hide... KB#213884. Keywords: Borders, Colors, Format, Gridlines, Outline, Patterns

 
233. Turn of gridlines in PowerPoint Mr Excel spent the last week in a grueling strategy meeting watching various presenters give PowerPoint presentations. When you build a table in Excel with the intent of pasting that table into PowerPoint, it is easy to turn off the gridlines so that the PowerPoint slides look cleaner. MrExcel.com... Keywords: Gridlines, VBA, Web

 
234. Hiding your formulas Q. I've created some clever formulas, and I don't want anyone else to see them. Is it possible to hide the formulas but display the results? Every cell has two key properties: locked and hidden. A locked cell can't be changed, and the contents of a hidden cell don't appear in the formula bar when... Keywords: Charts, Format, Formulas, Password, Printing

 
235. Helpful Keyboard shortcuts common to Excel Control + "C": CopyControl + "X": CutControl + "V": PasteF2: Edit current cell (Makes cell references appear in colors for easy editing)F5: Go toF11: "Instant" chart.Shift + F3: Paste function wizardControl + F3: Define NameControl + "+": Insert Cells, Rows, Columns. Depends on the current... Keywords: Charts, Borders, Colors, Format, VBA, Web

 
236. Copying Subtotaled Data Great question. When you use the Group and Outline buttons (or even the AutoFilter) to hide certain rows and then try to copy them, you find that Excel copies all of the hidden rows as well. Luckily, there is an easy, if not intuitive, solution to the problem.First, highlight all of the totals like... Keywords: Outline, VBA, Web

 
237. Creating a transparent chart series Excel charts do not support color transparency. When you apply a color to a bar or column chart series, the color must be a solid color. Although the Fill Effects dialog box includes Transparency controls, these controls are disabled.This tip describes how to make a bar or column series transparent... Keywords: Bar Chart, Charts, SERIES, AutoShapes, Borders, Colors, Format, Gridlines, Patterns, Formulas, Printing

 
238. Changing the Default Cell Comment Formatting Comment command lets you annotate a cell by typing a comment. Once the comment is created, you can change its formatting. To do this, right-click the cell and select Edit Comment from the shortcut menu. When the comment is displayed, click anywhere on its border to select the entire comment object.... Keywords: Charts, Borders, Colors, Fonts, Format, Formulas, Printing, Object, Toolbar, Comments

 
239. How to use Keyboard Shortcuts to Select Border Styles in Excel This step-by-step article describes keyboard shortcuts that you can use to access the border styles buttons on the Border tab of the Format Cells dialog box. Keyboard Shortcuts for Border Styles Microsoft Excel includes keyboard shortcuts to... KB#277581. Keywords: Borders, Format, Styles

 
240. Color Shift Occurs When Printing in Windows 98 When you print a document from an Office 97 program, the colors contained in the document may not print as expected. For example, the color red prints as... KB#108729. Keywords: Colors, Printing

 
241. Highlight the active cell What a cool question. The trick is to use the Worksheet_Selection change event instead of the Worksheet_Change event. This special event handler macro has to be entered on the code pane associated with your particular worksheet. A discussion of how to find this in the project pane is at this... Keywords: Colors, Macro, VBA, Web

 
242. Label last point Step by step instructions for creating a chart that displays a value label on the last point. Based on the data... Keywords: Charts, Legend, Plot, SERIES, Borders, Colors, Format, Gridlines, Links, VBA

 
243. Identify Cells with Formulas Juan Pablo, the Excel whiz behind our Spanish-language MrExcel Consultoria Division, came up with this cool trick that relies on XLM:One *quick* method to do this is as follows.Select A1.Go to Insert, Names, Define.Put a name in it, like HasFormin the refers to put:=GET.CELL(48,A1)Now, select the... Keywords: Format, Patterns, Formulas, VBA, Web

 
244. Round time to the nearest quarter hour MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as... Keywords: SERIES, Custom Number Formats, Formulas, VBA, Web, ROUND

 
245. Avoid printing specific rows In some cases, you may wish to avoid printing certain rows in your worksheet. For example, the rows may contain confidential information, or intermediate results that need not be printed.To avoid printing specific rows, you can hide the rows before you print, and then unhide the rows after... Keywords: Charts, Format, Outline, Symbol, Formulas, Printing

 
246. Fun With Dummy Series Keywords: Add-In, Charts, SERIES, Download, Format, Gridlines, Web

 
247. Excel -- Conditional Formatting 3. Leave the first drop-down box set to Cell Value Is 4. In the second drop-down box, choose one of the operators. In this... Keywords: Download, Borders, Colors, Conditional Formating, Fonts, Patterns

 
248. Eliminate "/" from File Names when Saving with a Macro The problem arises when the format of the date includes the “/” character… try the following:ActiveWorkbook.SaveAs Application.Substitute(Range(“A38”).Text,”/”,”-“) MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied,... Keywords: Format, VBA, File Name, Web, NOW

 
249. Layouts for column charts The layout of a chart can make a big difference in its legibility. Consider the two charts shown below. Both use the same data. The top chart contains two data series, one for each region. The bottom one uses six data series, one for each month. Which is better? There is no correct answer. If your... Keywords: Charts, SERIES, Format, Formulas, Printing

 
250. German Reports in Microsoft Excel Format Contain Incorrect Dates In the German version of Report Writer, all reports that are saved in Microsoft Excel format contain a wrong date for Datum der analyse, Erstes Datum analysiert, and Letztes Datum... KB#226500. Keywords: Format

 

<< (Page 4)  Previous              Next  (Page 6) >>