Home > Excel Help Portal > Excel Charts

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

Excel Charts

The ExcelUser portal offers links to help pages created by top
Excel web sites. This section discusses using charts in Microsoft
Excel.

151. Text value response to input Sure, Charlie, it would take a simple If formula. These come in real handy the next time you have a chance to pull off an incredible upset. Try a simple formula like this in cell A1:=IF(Bartolo="Weary","Absolutely Pull Him Out of the Game","Pull Him Out of the Game Anyway")Congratulations to all... Keywords: SERIES, Formulas, VBA, Web

 
152. 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

 
153. Dashed Series Lines Appear Solid in Excel 2000 When you open a file in Microsoft Excel 2000, some of the charts are displayed differently than they were in earlier versions of Excel. Data series that were displayed as a dashed line in earlier versions of Excel now appear solid. The data series... KB#248223. Keywords: Charts, SERIES

 
154. 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

 
155. Sum the largest values in a range Q. I need to calculate the sum of the three largest values in a range of 100 cells. The range isn't sorted, so I can't use a SUM function. Do you have any suggestions about how I could handle this problem? Excel's LARGE function returns the nth-largest value in a range, in which n is the function's... Keywords: Charts, SERIES, Format, Arrays, Formulas, Printing, LARGE, ROW

 
156. 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

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

 
158. Forecasting Techniques Example of applying Excel's forecasting features to the process of forecasting sales. This method provides the user with a method of eliminating seasonal trend and the R^2 statistic to compare the fit of various shape curves to the adjusted data. You can use Excel's built-in forecasting features to... Keywords: Charts, Plot, SERIES, Time Series, Download, Forecasting, Sales, Format, Formulas

 
159. Contextures -- Excel Chart Links Links to Samples and Articles Contact:... Keywords: Add-In, Charts, GANTT, SERIES, Whisker, Download, Links, Printing, Macro, VBA

 
160. 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

 
161. Excel 2000 Chart Object Becomes Picture In Word 7.0, when you open a Word 2000 document that contains a Microsoft Excel 2000 chart object, and you double-click the Excel chart object, the Word picture editor is opened instead of... KB#212228. Keywords: Charts, Object

 
162. 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

 
163. Unlink a chart series from its data range Normally, an Excel chart stores data in a range. If you change the data in that range, the chart updates automatically. Sometimes you may want to unlink the chart from its data ranges and produce a static chart that remains unaffected by later changes in the data. For example, suppose you plot data... Keywords: Charts, Plot, SERIES, Format, Arrays, Formulas, Printing

 
164. Cell Formulas Change When You Copy a Worksheet with an Embedded Chart When you copy a worksheet from one workbook to another, the formulas that already exist in the destination workbook... KB#265018. Keywords: Charts, Formulas

 
165. How to create a "Top 9" If your races are in A1:A11, try with this formula=IF(COUNT(A1:A11)9)*SMALL(A1:A11,1)-(COUNT(A1:A11)10)*SMALL(A1:A11,2)) 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... Keywords: SERIES, Formulas, VBA, Web

 
166. 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

 
167. 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

 
168. Data Input and Analysis Learn how to link named ranges, the Data Form, PivotTables and charts to produce an information system. Use a button to add data to the worksheet and a Vlookup formula to link Excel lists. You can link charts, PivotTables and Ranges of data to produce a basic information system. With a command... Keywords: Charts, Download, Format, Formulas, PivotTables, Links

 
169. Link Chart to Worksheet Range Demonstration of two methods of linking a chart to a worksheet range. When data in the range changes, the chart updates automatically. When you create a chart, the source range for the chart will not change as new data is entered into the worksheet. If you specify a named range as the source for... Keywords: Charts, Download, Format, PivotTables, VBA, Links

 
170. 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

 
171. 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

 
172. 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

 
173. Duplicate repeated entries in a list You've probably seen an Excel worksheet (like the sheet on the left, below) in which one entry in column A applies to several rows of data. Sort such a list and you get a real mess, because rows with empty cells in the sort column move to the top or bottom (depending on the sort order). When a list... Keywords: Charts, Sorting, Format, Formulas, Printing

 
174. Saving a chart as a GIF file Q. How can I save a chart as a separate GIF file? You can save your worksheet as an HTML file, and Excel will automatically convert any charts to GIF files. If that seems like overkill, you can write a simple macro that will do the job. Press Alt-F11 to activate the Visual Basic editor. Select your... Keywords: Charts, Format, Errors, Formulas, Printing, VBA, Macro, Files, HTML

 
175. Count AutoFiltered rows Q. When I use Excel's AutoFiltering, the status bar displays the number of qualifying rows. But for no apparent reason, that number often vanishes. How do I keep this number visible while I work? AutoFiltering a list hides rows that don't meet your filter criteria. After applying filtering... Keywords: Charts, Criteria, Download, Format, Formulas, Printing, SUBTOTAL

 
176. Working with pre-1900 dates In the eyes of Excel, the world began on January 1, 1900. Excel is not capable of working with dates earlier than that. People who use Excel to store historical information often need to work with pre-1900 dates. The only way to create a date such as July 4, 1776, in Excel is to enter it into a... Keywords: Add-In, Charts, Dates, Format, Formulas, Printing

 
177. Referencing a sheet indirectly Q. My Excel workbook has a sheet for each month, named January, February, and so on. I also have a summary sheet that displays key calculations for a particular month. For example, one of my formulas is: =SUM(February!F1:F10). Is there any way that I can enter the month name into a cell on my... Keywords: Charts, Sorting, Format, Formulas, Printing, INDIRECT

 
178. Counting distinct entries in a range Q. Can I write a formula that returns the number of distinct entries in a range? First, let's clarify the question. We're hunting for a formula that, given the range that contains the values 100, 99, 98, 100, 98, 100, 98, would return 3. In other words, this range contains three different values,... Keywords: Charts, Format, Arrays, Errors, Formulas, Printing

 
179. Animated Line Chart This example uses a Visual Basic for Applications procedure to update and display different values for a line chart. Each value is displayed for 1 second. You can use the Wait method in Visual Basic for Applications to animate the display of a chart. If you use the Wait method and change the... Keywords: Charts, SERIES, Download, Format, VBA

 
180. Click on Chart to Select a New Source Range In this example, you can click on a chart and enter a new source range. The chart will redraw to show the data in the range you select. You can create a Visual Basic for Applications procedure to prompt for a chart's input range. When you click on the chart, the prompt allows you to enter or select... Keywords: Charts, Download, Format, Object, VBA

 
181. Converting non-numbers to actual values Q. I often import data into Excel from various applications, including Access. I've found that values are sometimes imported as text, which means I can't use them in calculations or with commands that require values. I've tried formatting the cells as values, with no success. The only way I've... Keywords: Charts, Import Data, Format, Formulas, Printing

 
182. Importing a text file into a worksheet Excel users have long envied 1-2-3's ability to insert the contents of a text file into a worksheet. Until Excel 2000, all versions of Excel required you to open the text file separately, then copy and paste the data to your worksheet. Excel 2000 (and later) makes the process much easier. You might... Keywords: Charts, Query, Import Data, Format, Formulas, Printing, Text Files

 
183. 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

 
184. Chart Controlled With List Box This example uses a list box and worksheet formulas to change a chart's source range using a list box. You can use a list box to change the values in a chart by using the list box control ranges and the Indirect function. When the list box selection changes, the value of the reference used by the... Keywords: Charts, Download, Format, Formulas

 
185. Broken lines The breaks in the line are masked by the use of a dummy data series. This dummy data series is formatted with the same colour as the plot... Keywords: Plot, SERIES, Colors, Formulas, Links, VBA, NA

 
186. 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

 
187. 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

 
188. 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

 
189. 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

 
190. Delete single legend entry With charting it is sometimes necessary to use additional data series in order to achieve the effect in a chart you want. See my Label last point... Keywords: Charts, Legend, SERIES, Links, VBA

 
191. Increase the number of rows or columns? Q. How can I increase the number of rows or columns in a worksheet? In Excel, every workbook has 256 columns and 65,526 rows. Versions prior to Excel 97 have only 16,384. These values are fixed and cannot be changed. Despite what must amount to thousands of requests, Microsoft refuses to increase... Keywords: Charts, Query, Format, Formulas, Printing

 
192. GANTT Chart for Scheduling Resources Create a GANTT chart showing when resource is scheduled using a bar chart and Visual Basic for Applications procedure. You can create a Visual Basic for Applications procedure that uses a list of start and stop times on a worksheet to build a table of durations for a bar chart. The bar chart can... Keywords: Bar Chart, Charts, GANTT, Download, Format, VBA

 
193. 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

 
194. Creating a linked picture of a range Many users overlook a useful Excel feature that can create a live "snapshot" of a range and placing it anywhere you like. To use this feature: Select a range of cells Choose Edit, Copy Hold down the Shift key, and then choose Edit, Paste Picture Link. The result is an image of the selected range... Keywords: Charts, SERIES, Format, Formulas, Printing, Links

 
195. Chart Controlled With Button Example of control buttons on a chart used to change the 3D view and trend line settings. You can use a very simple Visual Basic for Applications procedure to call any one of Excel's hundreds of built in dialogs. This example shows the Dialogs method used to call the 3d view and trend line dialogs.... Keywords: Charts, Trend line, Download, Format, VBA

 
196. Using a workspace file If you usually work with a number of workbooks simultaneously, you might like the idea of opening all of your workbooks exactly where you left off the last time you closed Excel. If so, you need to create a workspace file. Before you quit for the day, select Files, Save Workspace and specify a file... Keywords: Charts, Format, Formulas, Printing, File Name

 
197. Plotting a Time Series of Data An XY scatter chart can be used to plot time series data. Chart options can be used to control display of lines between points. You can use the Chart, Options menu choices to control the look and feel of an XY scatter chart. In this example, the option Interpolate between cells is used to connect... Keywords: Charts, Plot, SERIES, Time Series, Download, Format

 
198. 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

 
199. 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

 
200. 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

 

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