Home > Excel Help Portal > Formatting Excel

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

Formatting Excel

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

251. Find the elapsed time between two time entries 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: Days, Custom Number Formats, Formulas, VBA, Web, MAX, ABS

 
252. Thai Bhat currency Keywords: Dates, Custom Number Formats, Formulas, Links, Files, Web, Comments, BAHTTEXT

 
253. JPEG Picture with Border Is Saved as GIF When Using Save as Web Page When you use Save as Web Page for a slide that contains a JPEG picture with a border, the picture is saved in GIF format rather than JPEG... KB#249057. Keywords: Borders, Format, Web

 
254. Converting Hours in Decimal Form to HH:MM:SS Format This article shows you how to convert hours displayed in decimal form to a time format such as hh:mm:ss (for example, converting 2.1 to... KB#214125. Keywords: Format, Formulas

 
255. Excel Code Examples This counter was built for a particular function. We had a spreadsheet in which a customer keyed a group of numbers then before submitting it for final approval, our program did a series of edits to find out if the numbers fit the tolerance. If they do not meet tolerance, the routine colors the... Keywords: SERIES, Colors, Fonts, VBA

 
256. Re-Arrange a Date Here is a quick and dirty to rearrange the order of elements in a date to display it in a totally different format. I have made it modular so you can change the parts around quickly to fit your... Keywords: Dates, Format, VBA

 
257. Using conditional formatting Excel's conditional formatting feature (available in Excel 97 or later) offers an easy way to apply special formatting to cells if a particular condition is met. This feature is even more useful when you understand how to use a formula in your conditional formatting specification. The worksheet... Keywords: Charts, Format, Formulas, Printing

 
258. Add Percent Labels to a Bar Chart Example of special data labels on stacked bar chart. The data labels show values from the source data range of the chart, but the series associated with the labels is hidden so only the labels are visible on the chart. You can add special data labels to a stacked bar chart and have the labels... Keywords: Bar Chart, Charts, SERIES, Download, Borders, Format, Patterns

 
259. Setting a font name with VBA ActiveCell.FormulaR1C1 = "x" I want to know how to change the font from arial to wingdings in my macro. Can you help me figure out the code to format my active cell with a specific font and assign my "*" symbol??? Thank you for your help."Ranges have a Font object, that handle all the things that... Keywords: Fonts, Format, Macro, VBA, Web

 
260. Keeping Excel from changing data to dates Two options, format the Column (Or range) where you will input the data as TEXT, Before inputting the data. Second, input the text with a ' starting, like this'1-2and Excel won't translate it. MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either... Keywords: Format, VBA, Web

 
261. Apply Styles to PivotTables This example worksheet demonstrates how to apply special styles to parts of a pivot table. When you change a pivot table it will lose its formatting. You can create a Visual Basic for Applications procedure that will apply styles to each portion of a pivot table. If you use the Format, Styles... Keywords: Download, AutoFormat, Styles, PivotTables, VBA

 
262. Format cells to display in thousands Q. Is there a way to display thousands without zeros? For example, I would like 52,000 to appear as 52. First, select the cells to be formatted, then choose Format, Cells. Click the Number tab. Select Custom from the Category list, and in the Type box enter 0, (that's a zero followed by a comma).... Keywords: Charts, Format, Formulas, Printing

 
263. Rotating Text With an AutoShape If you use Excel's AutoShapes, you've probably discovered a limitation: If you add text to an AutoShape, the text is not rotated when the AutoShape is rotated.Method 1 Here's one way to circumvent that problem: Create your AutoShape, add text, and format it to your liking. Ctrl+Click the AutoShape... Keywords: Charts, AutoShapes, Graphic, Format, Formulas, Printing

 
264. Display multiple charts on a single Chart sheet An Excel chart can appear embedded in a worksheet or reside in a separate Chart sheet. Here's a trick for storing multiple charts on a single Chart sheet. Create charts as usual, placing them in a worksheet. Select any blank cell in the worksheet and press F11; this creates an empty Chart sheet to... Keywords: Charts, Format, Formulas, Printing, Object, Window

 
265. Creating a non-graphic chart directly in a range This tip describes how to create a non-graphic chart. It uses formulas to display crude "bars" directly in a range of cells. The figure below shows an example of what you can produce with his technique. The formulas in columns E and G graphically depict monthly budget variances by displaying a... Keywords: Charts, SERIES, Fonts, Format, Formulas, Printing

 
266. Change Cell Formatting Use a VBA subroutine to shade selected cells on the worksheet. This example show how to add a Do Loop and With statement to a recorded macro. You can modify recorded macros with Do Loop and With, End With to create Visual Basic for Applications procedures that perform an operation on a block of... Keywords: Download, Format, Macro, VBA

 
267. Highlight Exception Values This example displays a date less that the current date in red. It uses a Visual Basic for Applications procedure to set the color of the text. You can use the OnEntry property to run an exception check. The procedure can change the font color of the cell and can perform an operation on the... Keywords: Download, Colors, Fonts, Format, OnEntry, VBA

 
268. Dealing with negative time values Because Excel stores dates and times as numeric values, it's possible to add or subtract one from the other. By default, Excel uses a date system that begins with January 1, 1900. A negative time value generates a date/time combination that falls before this date, which is invalid. The solution is... Keywords: Charts, SERIES, Dates, Format, Formulas, Links, Printing, Files

 
269. Excel -- Conditional Formatting -- Multiple Cells download a sample file 3. From the first drop-down list, choose Formula Is 4. In the text box, enter a formula that refers to the active cell in the selection. In this example, the formula is: =$B275 Use an absolute reference to column B ($B), to ensure that... Keywords: Download, Colors, Format, Formulas

 
270. Rounding time to a specific value If you have 8:15:00 in A1, use this formula (And format as General, or as Number with 2 decimals)=A1*24 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... Keywords: Format, Formulas, VBA, Web

 
271. Color Cells based on Criteria Keywords: Criteria, Colors, Fonts, VBA

 
272. Handle missing data in a line chart When you create a line chart in Excel, missing data points (blank cells) won't be plotted, and the line will contain gaps. Excel provides two other ways of handling missing data: Treat blanks as zeros Interpolate the data by connecting the line between the nonmissing data points. The figure below... Keywords: Charts, SERIES, Format, Formulas, Printing, NA

 
273. Parsing with characters Cells. On the numeric tab, select Custom. In the custom box, type 99\/9999\/99When you are typing custom number formats, you can insert any single text character by preceeding it with a backslash. MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty... Keywords: Custom Number Formats, VBA, Web

 
274. Convert Text to Numbers Keywords: Format, Formulas, VBA

 
275. BMP to charts and cells The two workbooks take a normal BMP image file and load it into Excel either as XY-scatter chart or directly into a... Keywords: Charts, Plot, Colors, Files

 
276. Changing a cell's background color You have to use the Interior property of cells, and then, you can use either ColorIndex (To use one of the 56 "presets" colors in Excel) or Color, like thisActiveCell.Interior.ColorIndex = 36orRange("A1:A6").Interior.Color = RGB(200,160,35) MrExcel.com provides examples of Visual Basic procedures... Keywords: Colors, ColorIndex, VBA, Web

 
277. Displaying a value in an AutoShape You want to make a particular value really stand out? Use an AutoShape. First, click the AutoShapes button on the Drawing toolbar, select the shape you want to use by clicking it, and click in the formula bar. Then enter a cell reference such as the following: =$B$14 Press Enter. In the example... Keywords: Charts, AutoShapes, Graphic, Format, Formulas, Printing

 
278. Text and values in one cell Did you know that you could combine text and values in a single cell? For example, assume cell A12 contains the value 1435. Enter the following formula into another cell: ="Total: "&A12 The formula cell will display: "Total: 1435." The ampersand is a concatenation operator that joins the text... Keywords: Charts, Format, Formulas, Printing, TEXT, NOW

 
279. Navigating Excel's sheets Every Excel user knows that you can activate a different sheet in a workbook by clicking its sheet tab. Most users also know that you can press Ctrl-Page Up to activate the previous sheet, and Ctrl-Page Down to activate the next sheet. But if your workbook contains many sheets, and not all of the... Keywords: Charts, Format, Formulas, Printing, Window

 
280. Convert 11.5 to 11 minutes and 30 seconds Use this formula=A1/(24*60)Where A1 houses the 11.5. You may need to format this cell as mm:ss 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... Keywords: Format, VBA, Web

 
281. Creating an Automatic Copy of a PivotTable Simple VBA macro to change PivotTable and create copy on another worksheet. You can copy a PivotTable to another worksheet and manipulate the copy in ways not permitted in the PivotTable. With a few lines of VBA code, you can automatically update the PivotTable and update the copy. The PivotTable... Keywords: Download, Format, PivotTables, Macro, VBA

 
282. Cascading Style Sheet Keywords: Colors, Styles, Formulas, Links, VBA, Web

 
283. Round values to the nearest fraction Q. Is it possible to round a dollar amount to the nearest 25 cents? For example, if a number appears as $1.65, I would like to convert it to $1.75. Excel's ROUND() function seems to work only with whole numbers. Yes, you can use Excel's ROUND() function to achieve the rounding you want. The... Keywords: Charts, Format, Formulas, Printing, ROUND

 
284. Writing a Flat File There is probably an easier way to do this, but my customer wanted an Excel written to a flat file in a specified format. In a hurry, of course! And it had to have a pre-defined number of spaces between each item across the... Keywords: Format, Printing

 
285. Displaying Sequence of Colors or the portion of another picture that overlays the cell. Guide to the Example: A VBA macro is called from the Display Colors in Cell button. It displays the colors that overlay the cells A1 to A14. Download an Example File in Excel 5 format: Lacher42.XLS (104KB) - about two minutes to download... Keywords: Download, Colors, Format, Macro, VBA

 
286. Alternate row shading using Conditional Formatting One way to make your data legible is to apply cell shading to every other row in a range. Excel's Conditional Formatting feature (available in Excel or later) makes this a simple task. Select the range that you want to format Choose Format, Conditional Formatting In the Conditional Formatting... Keywords: Charts, Colors, Format, Patterns, Formulas, Printing, MOD

 
287. Calculate the day of the year and days remaining If you've ever had to figure out which of the year's 365 days a particular date falls on, or how many days remain in the year, you've probably found that Excel lacks functions to perform the calculation. But you can create formulas to do the job. The formula below returns the day of the year for a... Keywords: Charts, Days, Format, Formulas, Printing, DATE

 
288. Change the formatting of your subtotal rows Excel's Data, Subtotals command inserts subtotal formulas into a list. This is a very useful command, but it's often difficult to identify the subtotal rows. You may want to make the subtotal rows stand out by applying special formatting. However, this can't be done by any of Excel's autoformats.... Keywords: Charts, Format, Outline, Formulas, Printing

 
289. Calculating a conditional average In the real world, a simple average often isn't adequate for your needs. For example, an instructor might calculate student grades by averaging a series of test scores but omitting the two lowest scores. Or you might want to compute an average that ignores both the highest and lowest values. In... Keywords: Charts, SERIES, Format, Formulas, Printing, AVERAGE

 
290. User Defined Function for Week Number Example of UserDefined function that returns the week number of any date. You can use the "ww" value of the Format function to return week number. This value is not available in the user interface. If you include it in a User Defined Function, you can use it to calculate week number in worksheet... Keywords: Download, Format, Formulas, User Defined Function

 
291. Custom Chart Series Formatting Excel offers a wide variety of chart formats from its lists of built in choices: Nine marker styles; 56 colors to be applied to marker foregrounds and backgrounds; 56 colors and a handful of line thicknesses and styles for lines and borders; innumerable gradient effects and fill patterns. Despite... Keywords: Charts, AutoShapes, Colors, Format, Patterns, Styles

 
292. Summing times that exceed 24 hours Q. I have a range of time values, but when I try to sum them, the total is never greater than 24 hours. When you add a range that contains time values, Excel ignores the hours that exceed 24. The solution is to use a custom number format. Using brackets around the hour portion of the format string... Keywords: Charts, Custom Number Formats, Formulas, Printing

 
293. Changing the number of sheets in a new workbook By default, each new Excel workbook begins life with three worksheets. You can, of course, add more sheets to the workbook or delete sheets you don't need. The unused sheets don't occupy additional memory or increase file size, but I generally don't like them in my workbooks. A better approach is... Keywords: Charts, Format, Formulas, Printing

 
294. Forget your password? Q. I saved my workbook with a password, but Excel doesn't recognize it and won't let me open the file. Am I out of luck? First, remember that passwords are case-sensitive. So if you originally entered your password as Xyzzy, typing xyzzy won't work. If you know you're entering the password... Keywords: Charts, Format, Formulas, Password, Printing

 
295. Hierarchical Listbox The class ajpHList allows you to use a normal userfrom Listbox in a similar way to a Treeview list....Where Nodes (listitems) are stored within other Nodes. Appropriate Nodes can be opened to reveal related nodes or closed to conceal them. Opening and closing of Nodes can be done either with double... Keywords: Fonts, Links, Class, UserForm, VBA

 
296. Override Excel's Text Import Wizard If you import text files into Excel, you've undoubtedly encountered the Text Import Wizard, which guides you through parsing the text. If the files you import are always parsed correctly, you may prefer to bypass this wizard and accept the defaults. To do so: Select File, Open Locate the file to be... Keywords: Charts, Format, Formulas, Printing, Files, Text Files

 
297. Making a worksheet very hidden You can use Excel's Format, Sheet, Hide to hide an entire worksheet. But unless you password-protect the workbook structure, anyone can select Format, Sheet, Unhide to see the hidden sheet. If you use Excel 97 or later, here's another option: To unhide the sheet, repeat these step, but set the... Keywords: Charts, Format, Formulas, Printing, VBA

 
298. Close all workbooks quickly If you find yourself with many workbooks open at the same time, you may be interested in a "hidden" command that will close all workbooks in one fell swoop. The trick is to press Shift while you click the File menu. When you do so, the Close command turns into the Close All command. When you select... Keywords: Charts, Format, Formulas, Printing

 
299. Fix incorrect decimal places during data entry Q. When I enter a value, it appears with two decimal places. For example, when I enter 154 it shows up as 1.54. What's wrong? Somehow Excel's fixed-decimal mode was turned on. To return to normal, select Tools, Options to display the Options dialog box. Then click the Edit tab and remove the... Keywords: Charts, Format, Formulas, Printing

 
300. Resize Excel's sheet tabs If your eyesight isn't what it used to be, you may want to increase the size of the sheet tabs displayed in your Excel workbooks. This parameter is determined by a Windows system-wide setting. To change it, select Start, Settings, Control Panel and double-click Display. In the Display Properties... Keywords: Charts, Format, Formulas, Printing

 

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