Free Microsoft Excel 2013 Quick Reference

Data disappears Results

When I attempt to sort a particular Excel spreadsheet, that data disappears,
except for the first cell in the column that I am sorting by. That cell
shows the number '1'.

Please offer suggestions, I appreciate it.


I have a table in an Excel 2008 file that is pulling data from an Access database query. I then have a pivot table that is using that table as it's source data. I just opened my file after not looking at it for a while and went to refresh my pivot table. When I do that, all of the data disappears and I am left with my grand total line showing all zeros. I tried to change the source of the pivot table and re-set it to the same source table, and it retains the data. But if I try to refresh it again, it all goes to zero again.

Please help me!


When I attempt to sort a particular Excel spreadsheet, that data disappears,
except for the first cell in the column that I am sorting by. That cell
shows the number '1'.

Please offer suggestions, I appreciate it.

Is there any way (VBA or other) to prevent the data in a graph from disappearing when you hide the rows that contain the graph's data?
(Particularly without storing the data in a hidden sheet...)


Hi - I am utilizing pivot tables to create 'data sets' that will be copy/pasted into another excel workbook. I am taking the larger set of data and breaking it down into subsets (our branches). I have months across the top and variables down the left side.

I need the data sets for each branch to have exactly the same number of columns (months) and rows (variables) every time, even if that branch did not have data for that month or variable. Is there a way to set a standard table that will not change as I chose the individual branches? (As I choose the indiv branches, the months or variables that don't have data disappear which will cause problems when pasting the data).

I've tried different ways of structuring the data (adding the branches along the top or down the side) to 'freeze' the table but there is too much data to do this.

Hopefully I'm explaining this well enough - does anyone have any suggestions? Thanks.

How do I keep the data visible when I hide the columns that contain the data. Thanks

I opened an EXcel worksheet, a message came up. Without reading it, I
clicked "yes" and all my data disappeared. The history shows 13.5 kb of data
but I cannot retrieve it. It was stored on a hard disk.

while I'm working with excel, the existing data in cells disappear, stop
being seen. I select the rows with the mouse and my data is back! I had the
same problem with Excel 2002, however after an office update it stopped. Now
I'm using Excel 2007 (office 207 ultimate), I had all the patches but the
problem is still there. I think it's some kind of a bug, but nobody else
seems to have it, but me!!!

I am able to get information from another workbook by referencing a
cell containing the file name:


In J38 there is: [K20060711.XLS]1 - General Statistics

This works fine, until the file K20060711.xls is closed then all the
data disappears. How do I remedy this?

stuckupnorth's Profile:
View this thread:

I can select a few rows and say group. Then the status bar says its
connecting to the database and executing the query. Once it finishes though,
the data all disappears and I'm left with just the Row and Column headers.

Any ideas?


I'm just learning to work with Pivot tables and charts. I have the following

I have a dataset with some columns, let's say one with 'Date' the other
three 'column 1', 'column 2' and 'column 3'.
I made a Pivot table of these. Put 'Date' in the row and the other three in
the column as 'sum of Column'.
When I make a graph of these, the legenda is either static and I cannot
choose just to show 'Column 2' for example of to show all Columns; or when I
can choose than after choosing all other columns disappear, in the graph as
well in the linked table. Very strange...
Another thing is that when I have negative numbers in two or more columns,
the Pivot graph (chart type is Stacked Area) only shows the negative numbers
of one column (it is not a Y-axis scale problem).
Please help...

Thanks guys!

i have a large amount of text in an Excel cell and it all shows but when i
click outside that cell, the last part disappears (it truncates in the middle
of a word). i have cells larger in other places in the worksheet. i tried
dragging down the cell size, merging the cells, and setting the size on that
row larger. same results each time. please help. Linda

Occasionally when I add new info to a cell using F2 the extra lines of type
show up when I hit F2 key then disappear when I hit enter like there is an
invisible page break not allowing extra data beyond it, but can see it when
hit F2.- puzzling? Excel 2002.

First off this is my first post; sorry if there are any problems with it. I will fix promptly if there are. I've gained a ton of information from this forum already so thank you to everyone.

I'm working with MS Excel 2003 and the workbook will be shared and protected. I've also enclosed a sample of it. This workbook will be used and constantly updated 24/7 with new data.

Short Story: I'm trying to find a macro that will copy data from the areas of B120:E179 and I120:K179 for example (linked to another worksheet within the workbook) and special paste (Values Only and skipping blank cells) it to the next available open cell up top where basic data entry will be taking place B10:E29 and I10:K29. I need it to only copy/paste the rows with data (skipping all cells/rows with no data) and once it is finished coping I will need it to place an "X" in column M next to the row that it copied data from. I would also need it to reference the data in each row from B to E and if there is an entry say on B14 to E14 that matches it but if I10 to E29 are blank then paste that information on row 14. If it does not match or if those columns are full then paste on next available line.

I hope I'm making sense here. This is for a vehicle tracking log between checkpoints. Each driver and info will be listed on each row. Columns B through E will contain information for each driver: name, badge, #passengers, and vehicle #. The log lists location, time, and destination for outgoing travelers in columns F to H. Incoming info is listed on Columns I to K. I'm trying to find a macro or some way to carry over the driver info and times from one checkpoint to another and fill it in properly on the log while at the same time not corrupting the conditional formats and that. Since this log will be constantly updating and information being added, I set up the referenced data in B120 and below that if an X is placed in column M then that data will disappear preventing the macro from reading it and duplicating it over and over on the log.

I understand this is a lot and possibly confusing so I've enclosed a sample of the log and you can see what I'm trying to achieve. If I left out any crucial information or if you need to know more just ask and I will reply with what you need. I did several searches and found some examples however they did not fully answer or help me out. Thank you to anyone willing to give me a hand on this one

Edit: I am adding this to hopefully make it easier to explain what I'm trying to do.

B10:E29 and I10:K29
B44:E63 and I44:K63
B78:B97 and I78:K97 reference the incoming travelers on the current Sheet2

B120:E179 and I120:K179 reference the outgoing travelers from Sheet1
B182:E241 and I182:K241 reference the outgoing travelers from Sheet3

Order of Events:

1) Check B120:E179 and B182:E241 for any data. (Called B120 & Below)

If there is data

2) Reference each row of data individually with the data from each row from B10:E29 and B44:E63 and B78:E97 (Called B10:E97)

If a Row of data (from B120:E179 and B182:E241) is the same data that already exists within B10:E29 and B44:E63 and B78:E97 and columns I, J, K are empty for that same row then

3a) Copy that same row of data from columns I, J, K (B120 & Below) and Special paste (Values Only) to columns I, J, K in the B10:E97 Rows.

If the Row of data matches but the I, J, K columns from B10:E97 are not empty then

3b) Copy the full row of data from columns B through E and I through K and Special paste (Values Only) to the next empty Row in the range B10:K97. Special Pasting Columns B to E and I to K (B120 & Below) to Columns B to E and I to K (B10:K97) and so on.

For each Row of data that does not match up with data from the B10:E97 range.

4) Copy the row of data from columns B to E and I to K (B120 & below) and Special Paste (Values Only) to the next empty Row in the range of B10:K97. The data from one Column (B120 & Below) will be special pasted to the same column (B10:K97) within the Row.

Once all the data has been Copied and Special Pasted or after completing each row

5) Place an X in Column M for each row that had data copied and special pasted (B120 & Below). This X in Column M will make that data disappear.

Now the Area of B120 & Below should be all copied and pasted to above log and should now be empty with X’s in column M from all the rows that previously had data.

It is important that the empty rows of B120 and Below be ignored through out this process as they may receive data later on to have this macro run again on them.

Also: that the Special Pasting pastes to the next available empty row, so that there are no gaps in the log. Unless of course, it was able to paste into a row of identical data as mentioned in numbers 2 and 3.

If anyone has an Easier/More effect way to update the other sheets outgoing traveler info into this sheets incoming info I’m open to any ideas. This workbook will be updating with new travelers throughout the day. Again thanks to all that help.

Make it possible to query any data from an Excel spreadsheet in exactly the
form it appears in the spreadsheet. The highest priority is to remove the
255 character limit from the size of fields being accessed in a query. I
suppose there may be some way to do this from SQLServer using a character
array, but if so, it is undocumented and probably not very developer
friendly. It is also necessary to remove the "feature" that makes
non-conforming numeric or text data disappear. The query should return all
text visible to the user in every row and column queried. The present OLE
DB interface causes data to disappear or even worse, appear in the wrong row.
This is a computational disaster and potentially a security problem.
Also, in the mean time, adding extra quote characters to tab delimited data
when saving as a text file further corrupts the data.

Hi, i am using excel 2003 SP3 and i have created a pivottable.

ive created one that is on a new worksheet in a file. It gets its data from 3 other worksheets in the same file. So there is one file in which the data (on 3 worksheets) and the pivottable (1 worksheet) are.

When create the pivottable it looks good and works all the way through. i've change the design a bit. all o.k.

Now when i email it to others users and they open it on their pc or laptop with various versions of Excel, every single one of them sees a blank pivottable. all the design and data are gone. See this image .

How can i fix this? i've googled my *** of but cant find an answer to this (simple?) problem..

Hi All,

I have a filter in my code that is supposed to filter data by a certain criteria, delete the filtered results and afterwards eliminate filter so that remaining rows would be seen by the next module.
The thing is it does filter correctly, but then if there is no data to delete (no data meets filter criteria), after removing the filter there is actually no data at all - it deletes the rows hidden by filter, it seems. If there is data that meets criteria and should be deleted then it deletes it and after removing the filter the data is ok. The problem arises when there is nothing to delete, it just deletes the hidden (by filter) rows.
Here is the code (the filter part is in bold):
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
    Rows("1:1").Font.Bold = True
    Range("L2:L" & Lastrow).FormulaR1C1 = "=DATEVALUE(RC2)"
    Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Select
    Selection.Sort Key1:=Range("A2"), Key2:=Range("L2"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    Range("J2:J" & Lastrow).FormulaR1C1 = "=COUNTIF(RC[-9]:R[+1]C[-9],RC[-9])"
    Range("K2:K" & Lastrow).FormulaR1C1 = "=R[-1]C+1"
    Range("K2:K" & Lastrow).Value = Range("K2:K" & Lastrow).Value
    Range("F1").AutoFilter Field:=10, Criteria1:=">1", Operator:=xlAnd
    Rows("2:" & Lastrow).Select
    Selection.Delete Shift:=xlUp
    Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Select
    Selection.Sort Key1:=Range("K2"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
This is a sample of the data:
Sample sorting.xls

Please advise me as to what is wrong with this code or how can it be improved. Any ideas appreciated.

I created a properly configured pivot table and have had certain numerical
data (#7) and the clock time of 1:00PM not appear in the pivot table under
the row and column headings. My data ranges are correct. If I go into the
raw data and substitute a different number or time, the pivot table will
properly reflect the change.

Hi, I am new to pivot table. I created a pivot table with the
"customer ID" as my row field, and "date of the month" as my column
field. Then I selected two fields as my data. One field is the
category of the things the customer bought and the other field is the
price associated with the item bought. Then in the data drop down I
can see these two fields. However, whenever I deselect one field, the
selection drop down disappears, my table then has only one data
dispalyed and I can't see the other field unless I add that field back
in. Is there a way to keep the selectiond drop down there, so I can
pick to see what I didn't select to see before? Thanks in advance.

I opened an EXcel worksheet, a message came up. Without reading it, I
clicked "yes" and all my data disappeared. The history shows 13.5 kb of data
but I cannot retrieve it. It was stored on a hard disk.

No luck finding an answer? You could always try Google.