|
Excel Charts
Various Examples
Excel Book
Excel Templates
Excel Seminar
Excel FAQs
Excel Hotline
Excel Colors
Excel Grid
Excel Brainteasers
Excel Tutorial (German)
2003 vs. 2007
Seminar Topics
Excel Templates
HI-CHART Templates '12
HI-CHART Templates '10
HI-CHART Templates '08
HI-CHART Templates '07
|
The following list of FAQs was compiled by Holger Gerths and is broken down into two groups, A Excel Charts and B General Excel Functions. It is comprised of questions he is most frequently asked in seminars, as well as in hotline e-mails and telephone calls.
The individual items are divided into these two groups and numbered. For a targeted search, it is best to use the search function at the top right.
We offer a hotline service (subject to charge) for questions about Excel. EUR 100 is charged for every half hour or part thereof.The first half hour is free of charge to former seminar participants.
|
|
|
|
 |
A EXCEL CHARTS
|
|
|
|
|
|
New 2007 Camera Workaround - see FAQ A 023 |
|
|
|
|
|
A 001 Column labels only possible inside |
|
|
I am unable to set the alignment of the labels of my column chart to outside, I am only able to select: end inside, centered, basis inside. Why is that the case?
|
|
|
The additional option you desire, End outside, is only possible for stacked columns and bar chart types, not for grouped types.
|
|
|
|
|
|
A 002 Automatic chart "clean-up" |
|
|
Why is there no macro to take care of the initial steps in cleaning up the chart? This would be a great thing to integrate into the available add-in.
|
|
|
Since completely new charts are not created that often and templates usually must be used instead, this task rarely is needed and which is why it had not been automated as yet.
|
|
|
|
|
|
A 003 Chart default settings |
|
|
It is possible to make a default setting in Excel in which hidden cells should always be plotted in charts? Or is it necessary to define this individually for each chart?
|
|
|
It is necessary to define it individually for each chart. The exceptions in this instance are user-defined chart types, or even better the chart template pages and/or folders we prefer. |
|
|
|
|
|
A 004 Small negative column labels are displayed on the positive side |
|
|
If I have a chart with positive and negative columns, after this is linked with PowerPoint the labels of small negative values are often displayed on the positive side. In Excel, however, it is displayed correctly.
|
|
|
This problem not only occurs in conjunction with a PowerPoint link. The best way to solve this is to create the label with an extra data series of dots. |
|
|
|
|
|
A 005 Variable chart data sources |
|
|
How can I design the size of the cell range to which the chart refers, for example, to change the width of the column chart from outside?
|
|
|
The Excel formula OFFSET is part of the solution here. Instead of a set area (like M1:T1, i.e. [Area-Start]:[Area-End]), this is designed dynamically: =M1:OFFSET(L1; 0; [Area-Width]). This formula is created as an Excel name. This name is used as the data source in the chart.
|
|
|
|
|
|
A 006 Excel crashes while navigating in the chart (versions up to XL 2003) |
|
|
If I navigate in the chart with the arrow keys (Cursor Up, Cursor Down), Excel crashes at a specific place.
|
|
|
The reason here can be a data series with a linked label. Hide all data points here by setting the X or Y coordinates to #NV, so that the data series can no longer be selected. If you attempt to navigate with the arrow keys to the position of this data series, Excel will crash. The solution: Add an invisible, yet non-NV dummy point to the series or hide it, not by using #NV, but by setting to 0. The point disappears because a column chart first begins with +0.5. This only works if the axis is not automatically enlarged in this direction. |
|
|
|
|
|
A 007 Extending templates |
|
|
I have extended one of the examples from your seminar to include a larger number of columns. Now there are incorrect values in the labels of the new columns in the chart.
|
|
|
This probably is due to the linked labels in the chart. The new labels which were added during the extension must also be linked to external cells. |
|
|
|
|
|
A 008 Linking chart labels to cells |
|
|
How can I make sure that labels of chart data series do not display the configured data series measures (X, Y, …), but rather an external value which I have calculated?
|
|
|
You can link the individual labels to those cells whose value and numerical format are expressed by the label. To do so, mark each separate label object, press the “=” key, click on the cells to be linked and finish with the Enter key. |
|
|
The best way to mark individual labels is to first mark the label row and then navigate with the right arrow key (→) to the corresponding label object.
This is simplified by using LINK function with the Chart-Me add-in. In this instance, mark the label row, call up LINK→START-LINK and then select all the cells to be linked. |
|
|
|
|
|
A 009 Unwanted breaks in Excel chart labels |
|
|
The labels in charts are broken after they have reached a certain length. I can’t find an option with which I can avoid a break. What can I do in this instance?
|
|
|
Formatting cannot prevent a break. If the label width exceeds approximately 19% of the chart width, a break must occur. To remedy the situation here, you must enlarge the entire chart by several sizes and then scale the visible data series again to the original width. A category axis works best by adding empty columns and scatter charts work best by introducing an X-scaling factor.
For XL 2007, it is sufficient to only enlarge the chart area and bring the drawing area back to its original size. Additional scaling measures are then not required. |
|
|
|
|
|
A 010 Copying pages with charts and names as the data source |
|
|
When I use names as the data source in Excel charts, these names will be resolved into the basis numerical values when copying the respective page into another folder or duplicating within the same folder. How can I prevent this from happening?
|
|
|
This is a common XL 2007 error. A page with Excel charts with names as the data source must be copied from Folder A to Folder B as follows:
1. Save Folder A
2. MOVE page to a new Folder B
3. Close Folder A without saving and open once again
4. Rename page in Folder B
5. MOVE page back to the existing folder
6. Test |
|
|
|
|
|
A 011 Axis groups and several charts |
|
|
I have used the primary and secondary axis groups and require a third axis group. What do I do?
|
|
|
Stack another chart with a transparent background over the existing one and make sure that the axes (X and Y) are exactly the same size. |
|
|
When a chart is marked in version Excel 97/2003 the background becomes white and transparent. |
|
|
|
|
|
A 012 Bar charts |
|
|
Bar charts are trickier to work than column charts because of the reversed coordinate system (X-vertical, Y-horizontal) compared to columns. This is due to the fact that dots in bar charts have no reversed coordinate system and can therefore not share an axis group with bars. As a result, it quickly becomes necessary to place a second scatter chart over the bar chart. Is there an easier way to do this?
|
|
|
This is simpler in XL 2002 or XL 2003. Here the chart can be created as a column chart with all labels rotated 90 degrees to the left. Then it is “photographed” with Excel and the camera object is rotated 90 degrees to the right. |
|
|
For versions older than XL 2002, the camera cannot be rotated and in XL 2007 the camera only functions to an extremely limited extent in connection with charts. |
|
|
|
|
|
A 013 Cross charts |
|
|
Is there a way to create cross charts in Excel?
|
|
|
Cross charts are charts which have several (usually 2) value axes and represent either dots or bubbles where the coordinates X, Y, … cross. Column or bar charts, which are often used, have only one value axis and a category axis with discrete categories which do not represent a number line. |
|
|
In Excel, one uses in this instance (in a 2D-space) the scatter chart type with two dimensions or the bubble chart type with diameter as a third dimension. |
|
|
|
|
|
A 014 Camera changes charts |
|
|
I have created a camera that displays a chart. Every time I open or print the print preview, the drawing area of the chart displayed with the camera is somewhat lower. What is this effect and how can I avoid it?
|
|
|
This is a common error which occurs when a chart is displayed with a camera and the chart falls short of a critical size. What the critical size is depends on which standard printer driver installed and this varies from one computer to another. You can avoid this effect by enlarging the chart n-times. Usually doubling is sufficient, you have to try it out. The font will also be enlarged by n-times. Then create the camera for the subsequently larger range and then make it smaller again to the previous size to 1/n. |
|
|
|
|
|
A 015 Displaying laterally offset red-green deviations |
|
|
How can I manage to visualize deviation columns or bars laterally offset?
|
|
|
To do this, create a second chart which is the same size and has the same axis scaling as the first chart. Make the drawing and chart areas transparent and place the second chart over the first chart displaced accordingly. |
|
|
This works particularly well when you first snap the second chart into place with the Alt key exactly over the first chart and then click on the chart again while holding down the Shift key and move it using the Arrow key pixel-by-pixel to the correct position. |
|
|
|
|
|
A 016 Distorted graphs in charts with a PowerPoint link |
|
|
When I insert graphs such as arrows or ellipses into scatter charts in Excel 2007, they appear extremely distorted when they are linked to PowerPoint.
|
|
|
This is basically an unresolved problem in Office 2007. In 2007, arrows are created using two points (start and end points) and a connecting line is created with an arrow point (new in 2007). Ellipses are drawn using several points with connecting lines. |
|
|
|
|
|
A 017 --- |
|
|
|
|
|
A 018 Dots are hidden in 2003 and visible in 2007 |
|
|
I have created charts using XL 2003 in which I implemented labels and graphs with scatter charts. In order to avoid crashing in Excel with #NV, for the Hide option I set the X coordinates to -1 instead of using #NV. In XL 2007 the chart margin on the left is now wider than before and the hidden points are now visible in the left margin.
|
|
|
In 2007 the minimum of the X axis as a text axis on the left is not predefined, but rather can be reduced automatically. Since XL2007 does not crash with #NV, #‘NV is now used to accordingly hide points. |
|
|
|
|
|
A 019 Storage location of the color designs in XL 2007 |
|
|
Where in the file system can I find the color designs of Office 2007?
|
|
|
Those are XML files, which are found by default under C:\Users\\AppData\Roaming\Microsoft\Templates\Document Themes\Theme Colors |
|
|
|
|
|
A 020 Extremely long calculation time when using a time axis in XL 2007 |
|
|
I have created a chart with a time axis and the time for recalculation is extremely long (several minutes). What's causing this?
|
|
|
When using a time axis, please make sure that scatter charts are not hidden by concealing the X coordinate (with #NV), instead only by hiding the Y coordinate. Otherwise, the maximum of the axis will be set to 32000 following a very long calculation time. |
|
|
|
|
|
A 021 Using a time axis in XL 2007 |
|
|
Why do the examples used in the seminars after the summer of 2008 almost completely omit time axes? What is used instead?
|
|
|
The scaling along the category axis is now carried out with OFFSET. In the current examples, the time axis is only used when a column chart with columns of different and variable widths is created from an area chart.
The reason for this is an error in XL 2007. If the time axis is used here, the points in the same chart can only be placed exactly on the interval boundary and no longer in between. |
|
|
|
|
|
A 022 Linked labels in XL 2007 |
|
|
I have an Excel spreadsheet with a chart, containing linked labels, which has been duplicated in the workbook. In the new spreadsheet, the linked labels still refer to the old original sheet.
|
|
|
That is a common error in XL 2007. Copy the spreadsheet instead in another folder, rename it there and then copy it back to the original folder. |
|
|
|
|
|
A 023 Excel Camera in XL 2007 |
|
|
I created an Excel Camera in XL 2007, which displays a chart in which I mark the cells behind the chart, copy them and then insert them on the target with Start-Paste as Picture-Linked Graph as a so-called Excel Camera. When I recalculate or open the workbook again, the camera is white. Why is that so?
|
|
|
This is a common error in XL 2007. Cameras on charts are (usually) empty if the charts are not visible at the time of calculation. For this reason, you have to give up using a camera until further notice in 2007 when it should display a chart. |
|
|
New Workaround!
If the chart is outside of the visible area, then simply enlarge the outside chart area so that it is then included in the visible area. Then reduce the inner drawing area again to its original size.
Tip: In this instance, (as of XL2007) Snap can also be activated with the ALT key.
|
|
|
|
|
|
A 024 Red-green charts in XL 2007 |
|
|
Does the trick with Invert if negative still work in Excel 2007 for red-green charts?
|
|
|
No, this trick doesn’t work in Excel 2007. The best way to create multi-color data series is to use a separately controlled data series for each color to be displayed |
|
|
|
|
|
A 025 Hatching in XL 2007 |
|
|
How can I hatch areas such as chart columns in Excel 2007?
|
|
|
Hatches have been omitted in Office 2007 charts. Probably in favor of area fills. Hatching can be added either via VBA or download and use the Excel add-in from Andy Pope under http://www.andypope.info/charts/patternfills.htm. |
|
|
|
|
|
A 026 Better image quality in PowerPoint
How can I improve the image quality in an OLE link from XLS to PPT, especially in the fonts?
Many Office users are familiar with this problem: Fonts often appear somewhat uneven when linked from Excel to PowerPoint –characters are not displayed as uniformly as in Excel.
Here is the solution:
First double the size of the chart and then double the font size in Excel.
Now link the cell range behind the chart via OLE to PowerPoint.
The link is then reduced by 50%, to retain the original size in PPT. This will greatly reduce distortions that occur primarily in the labeling.
This can be further improved by working with four or eight times the original size.
|
|
|
|
|
|
A 027 Better image quality with the Excel camera
How can I improve the image quality – above all regarding distortions and “rough spots” in the font – when using the camera in Excel?
Many camera users are familiar with this problem: irregularities in positioning and fonts displayed unevenly.
The solution to this problem is the same as for linking with PowerPoint (FAQ 026): First enlarge the original chart and the respective fonts (for example, 200%) and then use the camera image in the corresponding smaller size (in this example, 50%). |
|
|
|
|
|
A 028 Larger symbols and thicker lines in Excel 2007 and 2010
Odd visualization problems occur in Excel 2007 and 2010: When zooming at roughly 100%, point symbols appear much larger than in Excel 2003.
The symbol size appears the same as in Excel 2003first when you zoom to much larger sizes.
This phenomenon is particularly obvious when the symbols have an outer frame. This is the foreground color in Excel 2003 and the color of the indicator lines in Excel 2007 and 2010.
This effect is negligible if the frame color is set to "no color."
|
|
|
|
|
|
A 029 Navigating through several stacked charts
I have stacked 12 separate charts in a complex chart and positioned them uniformly with ALT.
Is there a shortcut to switch between individual charts without moving them manually?
The solution:
First click a cell to ensure that a chart is not selected.
Then:
- Versions up to XL 2003: Click the top chart while pressing SHIFT
- In XL 2007 and higher: Click the top chart while pressing STRG
Switch among individual charts with the TAB key (SHIFT-TAB moves in reverse).
The chart name is displayed in the Excel name box. To make it easier to navigate, it is advisable to assign a chart name if one does not exist yet.
- Versions up to XL 2003: To edit the chart, it must be moved.
This works best with the ARROW keys and the activated function “Align to Grid.”
This can (in the German version of Excel) be activated or deactivated with ALT – ZUR.
After editing, simply move it back with the ARROW keys.
Please note: This aligns only the edge of the chart pointing in the direction of motion.
- In XL 2007 and higher: All charts except the one clicked first are activated with the TAB key in editing mode.
Once you have reached the correct chart, you can navigate through the chart elements with the ARROW keys.
If you would like to move the selected chart, you must first do so with the mouse by the thick outer border or click it again while pressing STRG and then move with the ARROW keys. |
|
|
|
|
|
B EXCEL IN GENERAL
|
|
|
B 001 AutoFilter |
|
|
Is it possible to write the selection criterion in a field for an AutoFilter?
|
|
|
You can assign external values to an AutoFilter in Excel using Data-Filter-Advanced Filter. |
|
|
|
|
|
B 002 Decimal separator |
|
|
I am unable to calculate a result from values in cells with a formula. The calculation formula always displays #VALUE.
|
|
|
It is likely that a false decimal separator, for example a period instead of a comma, has been entered in one of the cells to be used for calculation. This cell is then treated as text. |
|
|
|
|
|
B 003 Number format Text |
|
|
I am unable to calculate a result from values in cells with a formula. The computational cell always displays the formula instead of the result.
|
|
|
It is likely that the number format Text has been entered in the computational cell. Change the number format to a different format, such as to Default. Then you need to edit the formula (F2) and complete with Enter. |
|
|
|
|
|
B 004 Excel Camera |
|
|
What is an Excel Camera?
|
|
|
A graph which displays a cell range as an image and updates the changes in the case of new calculations. |
|
|
The camera can only be created from copied cells, not from charts. However, the camera does display graphs and charts located within the copied cell range.
The camera only functions within Excel. |
|
|
|
|
|
B 005 Creating an Excel Camera |
|
|
How do I create an Excel Camera?
|
|
|
Mark the cell range to be photographed and copy it to the clipboard.
In Excel 2003: Mark a cell in the target area, click on the Edit menu while holding down the Shift key click and select "Insert Linked Image" from the menu.
In Excel 2007: Mark a cell in the target area and select Start-Paste-As Linked Image Graph from the menu. |
|
|
The camera objects should always be scaled to 100%, otherwise an undefined font size will occur. If the width and height are scaled differently, the entire image, and in particular the font, will be distorted. |
|
|
|
|
|
B 006 Linking from Excel to PowerPoint |
|
|
(Revised on 7/1/2010 RH)
How can I take tables, charts, or entire exhibits created in Excel and present them in PowerPoint?
|
|
|
By inserting the corresponding cell range from Excel into PowerPoint as a link. To do so, proceed as follows:
Just like for an Excel Camera, link only cell ranges here. It is important to display the areas in PowerPoint in 100% size. Otherwise, the font size above all is undefined in PowerPoint and possibly of different size on every slide.
In order to achieve this, you have to make the Excel area the same size viewed in a standard PowerPoint screen presentation, in a 4:3 format. In PowerPoint, the slide size can be looked up under (2003: File→Page Setup, 2007: Draft→Page Setup).
The default setting here is 25.4 by 19.05 cm – which corresponds to 10 by 7.5 inches.
Output devices like screens and printers have a ratio of how many pixels fit in one inch, in other words Dots Per Inches=DPI. This setting can be looked up under the graph settings. Most systems have a setting here of 96 DPI. Inch measurements can be multiplied with this and converted into pixels: For 96 dpi and 10 by 7.5 inches, this corresponds to 960 x 720 pixels.
To dimension an Excel image for PowerPoint in this size, create a row and column grid that adds up to 960 pixels wide and 720 pixels high. Rows and columns can be set in pixels by changing the height or width in the headers of the lines and columns with the mouse. A tooltip displays the current pixel size when dragging. To make this task easier, we have included the SIZE function in our Chart-Me add-in. You can download a trial version from http://itbuero.gerths.de/download/chart-me/ .
If you have created a cell range in Excel with a size of 960 by 720 pixels, proceed as follows to link it with PowerPoint:
(1) Mark the cell range in Excel and copy it to the clipboard.
(2) Open the corresponding presentation in PowerPoint or create a new page.
(3) Paste the copied area from Excel into this page as follows:
2003: Edit→Paste→Paste link→Microsoft Excel Worksheet Object
2007: Start→Insert→Paste→Paste link→Microsoft Excel Worksheet Object
|
|
|
(4) Directly after inserting the linked Excel image, you need to right-click on this image in PowerPoint and carry out Update Link. This is often forgotten, but without it the size of the link cannot be set without having to be changed again when it is updated.
(5) Afterwards, right-click again on the link in PowerPoint, set to Format Object→Size→Reset (to 100%).
(6) If everything is done correctly, the XLS image can then be snapped into the corners of the PPT image, accurate to the last pixel.
(7) Note: If corporate design elements are to be considered in PowerPoint, Excel will of course have to show transparent areas on these areas
(8) Note 2: If PowerPoint images should have other formats than those discussed above – for example A4 – then other pixel values of course will apply.
|
|
|
|
|
|
B 007 Creating charts in PowerPoint How can I create charts in PowerPoint?
To create sophisticated exhibits with charts in PowerPoint, we recommend creating them first in Excel and then linking them to PowerPoint.
There are two reasons why:
1. Excel exhibits can be designed in such a way that they can be updated dynamically in a data-driven manner and linked to external data sources.
2. Excel charts provide more design options than those created in PowerPoint.
If only the PowerPoint presentation is opened without the linked Excel folders, then the images will be displayed as they were when last saved in PowerPoint.
In particular for charts, the link from Excel to PowerPoint ensures that a vector graphic, which contains the information enabling an update, is copied from Excel to PowerPoint.
From a practical standpoint it is recommended to save the Excel and PowerPoint files in one folder.
The Office version plays an important role in linking. In Office 2007, there is a substantial loss of quality in any kind of OLE link. We, therefore, recommend switching directly from Office 2003 to 2010 whenever possible.
If the Excel sources are available, the changes made in Excel can be updated automatically in PowerPoint.
This method is particularly useful for recurring quarterly or monthly reports. If the report was prepared properly, monthly changes can be made with a minimum of effort.
In our opinion, there is no disadvantage compared to creating charts directly in PowerPoint.
|
|
|
|
|
|
B 008 Gridlines |
|
|
How can I show and hide gridlines in Excel?
|
|
|
In Excel 97/2003 either with gridlines or using the corresponding button in toolbar..
In Excel 2007 with View→Show/Hide→Gridlines.
|
|
|
|
|
|
B 009 Chart-Me add-in under XL 2007 |
|
|
I have integrated the current Chart-Me version into 2007, but nothing happens. Is it even compatible with 2007?
|
|
|
The add-in does indeed function in 2007. It appears in Excel 2007 in the menu bar under Add-Ins as a toolbar. |
|
|
|
|
|
B 010 Frames in the Camera and PowerPoint link are offset |
|
|
When I display a chart with an Excel Camera or link it to PowerPoint, the chart frames are offset towards the center by a few pixels and a border is shown outside which actually should not be visible.
|
|
|
Chart frames cannot be used here, use cell frames instead. These fit exactly and will not be offset. |
|
|
|
|
|
B 011 User-defined number formats |
|
|
What does the user-defined number format [>80]0;-0;"" that you use actually mean?
|
|
|
User-defined number formats can consist of a maximum of four segments, separated by semicolons. The first segment (here [>80]0) is responsible for positive numbers, the second (here -0) for negative, the third (here ““) for zero and the fourth (not used here) for texts. [>80] is essential here, displaying for the positive only numbers greater than 80 and hiding all other positive numbers. ““ in the third segment basically displays zero values as a blank space, in other words not at all. |
|
|
|
|
|
B 012 Excel templates as the default – like Normal.dot in Word |
|
|
Can I define a template in Excel that automatically serves at the basis for every newly created workbook, similar to Normal.dot in Word?
|
|
|
Yes, you can. Create a new workbook with the desired contents and save as file type.xlt (XL 2003), or.xltx (XL 2007, without macros) or .xltm (XL 2007 with macros).The file name must be Version Book, in XL 2003 thus Version Book.xlt and in XL 2007 Version Book.xltx or Version Book.xltm. There are two different storage locations for this template, one for a specified Windows users and one for all users:
Windows XP:
All users: C:\Programs\Microsoft Office\Office12\XLSTART and
Specified users: C:\Documents and Settings\[Username]\ AppData\Microsoft\Excel\XLSTART
Windows Vista:
All users: C:\Programs\Microsoft Office\Office12\XLSTART and
Specific users: C:\Users\[Username]\AppData\Roaming\Microsoft\Excel\XLSTART
The Office[s] directories have inserted the version number of the Office version. This has the following meaning:
Office10: Office 2002 = Office XP
Office11: Office 2003
Office12: Office 2007
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
