Friday, October 14, 2016

12 Months Data -13 Months Data in a Chart





An interesting Excel feature is that as you make the 12-month chart smaller and smaller, Excel drops the last (right-most) label from the X axis although the column or line still may appear in the chart itself. So, you may want to consider including thirteen months of data when putting together your dashboard.

Thursday, June 16, 2016

Excel 2016 Charts - X Axis Date Units

Date Units on the X Axis- Excel

I was working on a dashboard in Excel 2016 and I ran into something a bit frustrating. So, I thought I would share it since I am assuming others have run into the problem as well.
Below is a picture of my data and my chart - See anything wrong with it?
OK- now don't get too picky - this is before I formatted or updated anything - so what's wrong with it?


Take a look at my X axis..
The X axis shows 1/1/2016, 2/1/2016 instead of what is actually shown in Column A, which is the last day of each month.  I have used this same data and charted it in other versions and never ran into this particular issue so I was a  bit surprised.
Apparently Excel 2016 and probably Excel 2013's unit default is month.
To fix this problem, right-click on the X axis and on the Format Axis pane, change the Base to Days.



Once you do that, magically the dates in my chart now match the dates in my worksheet.








Tuesday, May 31, 2016

Updating Your Pivot Table

Automatically update your Pivot Table 


Whenever I start showing people Pivot Tables, one of the first questions asked is: “What happens when additional data is added to the source data?” 
First, changes to the existing data are automatically updated
 when you select the REFRESH button  located on the contextual Options tab.

If you add additional rows of data, Excel does not automatically add that information to the Pivot Table.
There are two ways to get Excel to automatically update the Pivot Table for any additional data.

ÜStarting with Excel 2010  you can turn your data into a table. If you do this, then Excel will automatically update the Pivot Table. This is by far the easiest way to do it.

Create a Table

Turn your data list into a table and it displays alternating row shading and AutoFilter drop down boxes.
1.      Click in the data.
2.      Click Insert tab.
3.      Click Table.
4.      Verify the data range and click OK.



That is it! Excel now considers your data to be a table and it will incorporate new rows and columns of information as  they are added.  If you open your Pivot Table and re-select your data source, the Pivot Table will now update as the range changes.  As data is added it will automatically be incorporated into the parameters of the original source data.

If you don’t control the data, then you may not be able to turn it into a table. In that case, the process is a bit more involved as you need to create a dynamic range, but it is definitely worth the effort.

Create a Dynamic Range

1.      Click in the source data.
2.      Select the Formulas tab and click on Define Name.
3.      In the New Name dialog box, name your data. In this example, I named it sales_data.
4.      The scope should stay at the default level of Workbook.
5.      In the Refers to: section type the following:



6.      =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
7.      Click OK.

What we have created  is  a dynamic range called sales_data. The OFFSET formula is telling Excel that on Sheet1, look at cell A1,  count how many cells are in Column A, and then it  counts how many cells are in row 1, so it will count and include new data as it is added.

Warning:  Make sure there is no other data below your dynamic range. If there is, this will not work.

Friday, February 12, 2016

Phantom Links in Excel Workbooks


Ever open up a worksheet and have an "Update Links" dialog box pop up and, even after searching, you can't find the link?

I have one worksheet that this kept happening in. Even trying to break the link wouldn't work.  Tom Urtis has provided a number of different methods to fix the problem and you can click here to see his solutions. He has a great tutorial on it.

If the first couple of his solutions don't work you and don't want to get into macros and you know it is a phantom link (so it won't impact your spreadsheet) then try my solution.

Click the Update Links icon in the dialog box  and Excel will display an  an Edit Links dialog box and it shows the data source. I tried breaking the link and I still got the Update Links message so I persevered.
Under the Edit Links dialog box is a Start Up Prompt icon.
Click on it and you can tell Excel to stop displaying the Update Links message when opening the file and to NOT update the links automatically.
It's not a perfect solution but at least you get rid of that annoying dialog box.