 |
|
|
One of Excels most popular features would arguably be its ability to chart data . Below are some of the tips and tricks I have found (mostly by accident) over the years. See Also Excel Charts Here
You have just spent hours creating your chart and its a looking exactly how you want it, the only problem is you cannot stand the thought of having to do the same thing all over again for your next chart(s). Below are three ways that will save you many hours.
Short and Sweet
Simply click on your chart to select it and then right click and
select Copy. Now all you have to do is paste your new chart where
you want it and change its Data range and Series.
If your chart is on a Chart sheet then click the sheet name tab and
hold down your Ctrl key and drag the sheet to another location.
User Defined
Right click on your chart and select Chart type then click the
Custom Types tab. At the bottom of this page you will see the Select from
box. From in here select User defined, now click Add. Type in a name
for your chart and a description if needed, then click OK.
Default to User Defined
Do exactly as above in User Defined but after clicking Add and
typing in a name and description click the Set as default chart button. Now
each time you create a chart it will default to your custom type.
A very common problem with charts is that if your data range contains empty cells Excel will try to plot them. This makes your chart drop off suddenly and leaves you looking at a chart with a lot of missing data. Below are two ways this can be overcome. The second method would probably suit most needs.
Hiding Rows or Columns
Let's assume you have a chart that is plotting sales figures by month. The month
names are in cells A1:A12 with their associated sales figures in B1:B12.
This would mean if the current month was April then your chart would be plotting
8 months of empty cells, as May:Dec figures are not yet available. To avoid
this, you could simply hide rows 5:12 (May:Dec). Excel will not plot hidden
rows. To hide these rows select them and go to Format>Rows>Hide.
Return #N/A
Using the same example as above in Hiding Rows or Columns follow these
steps:
As you enter the sales figure for the month you will type over the
=NA().
This will mean that if the sales figure has not been entered for any month both
the month column (Column A) and the sales figure column (Column B)
will return #N/A. Excel will not plot #N/A.
Let's assume you have a chart that is plotting dollar values in the range A1:A20. Now let's say sometimes you need to add a new series eg; some comparison Pound values in range B1:B20
Drag and Drop
Highlight the range B1:B20, place your mouse pointer over any
border of the highlighted range until it changes to an arrow. Now click
and drag the range onto the chart and drop it. You should now see the chart
Paste Special dialog box. You can also do this by copying your data
then choosing Paste Special from the Edit menu.
Give your charts a professional look by using a Scrollbar to increase and decrease the data range it is plotting. Just follow these easy steps:
Now use the scrollbar to change the data range in your chart.
Here is a great little trick that will allow you to change your chart range by selecting it from a list. This one should impress the boss!
Now select a name from your list in F1 and your chart series range will change accordingly.
See Dynamic ranges for full instructions and more
Expand Down One Row Each Month
In the Refers to box type: =OFFSET($A$1,0,0,MONTH(TODAY()),1)
Expand Down One Row Each Week
In the Refers to box type: =OFFSET($A$1,0,0,WEEKNUM(TODAY()),1)
You can also change the Columns the dynamic range will span by simply changing the last Argument of the OFFSET function to a higher number than 1.
You could even expand across your Columns dynamically by placing another COUNT or COUNTA formula as the last argument, instead of 1. See below:
In the Refers to box type: =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))
This dynamic range will now also expand across Columns in Row 1. So if you add another Column to your Table the dynamic range will automatically incorporate it.
To try and give you a better understanding of the OFFSET formula, read the text below taken from the Excel help file.
OFFSET(reference,rows, cols,height,width)
Reference : is the reference from which you want to base the offset. Reference must be a reference to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.
Rows : is the number of rows, up or down, that you want the upper-left cell to refer to. Using 5 as the rows argument specifies that the upper-left cell in the reference is five rows below reference. Rows can be positive (which means below the Starting reference) or negative (which means above the Starting reference).
Cols : is the number of columns, to the left or right, that
you want the upper-left cell of the result to refer to. Using 5 as the cols argument
specifies that the upper-left cell in the reference is five columns to the right
of reference. Cols can be positive (which means to the right of the Starting reference)
or negative (which means to the left of the Starting reference).
If rows and cols offset reference over the edge of the worksheet, OFFSET returns
the #REF! error value.
Height : is the height, in number of rows, that you want the returned reference to be. Height must be a positive number.
Width : is the width, in number of columns, that you want the
returned reference to be. Width must be a positive number.
If height or width is omitted, it is assumed to be the same height or width as reference.
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel
| MSSQL Migration
Toolkit |
Monte Carlo Add-in |
Excel
Costing Templates