Reinstall Office Right-click the “Start” button, then select “Control Panel“. Select “Programs“. Select “Uninstall a program“. Locate “Microsoft Office” in the list. Right-click it, then choose “Change“. Select “Quick Repair“, then click “Repair“. Once the repair is complete. Try to launch Excel. I am in Excel 2011. I click File, Open, and the page opens to choose your file. The page is frozen in the middle (like Freeze pane) and i can only see the top half of the page. How do I remove the.
Excel Is Frozen On My Mac
The Microsoft Excel program is designed not only to enter data into the table conveniently and edit it as you need it, but also to view large blocks of information. Most people using PCs work with it constantly.
The columns and rows names can be significantly removed from the cells with which the user is working at this time. Scrolling the page to see the name of the document is not comfortable for any user. Therefore, the Tabular Processor has possibilities to fix certain areas.
Locking a row in Excel while scrolling
Usually an Excel table has one cap. Meanwhile, this document can have up to thousands of rows. Working with multipage table blocks is inconvenient when column names are not visible. Scrolling the page to its beginning to return to the correct cell is absolutely irrational. To make the cap visible when scrolling, fix the top row of the Excel table, following these actions:
- Create the needed table and fill it with the data.
- Make any of the cells active. Go to the “VIEW” tab using the tool “Freeze Panes”.
- In the menu select the “Freeze Top Row” functions.
Excel Is Frozen On My Mac
You will get a delimiting line under the top line. Now when you scroll vertically the table cap will be always visible.
Locking several rows in Excel while scrolling
Let us suppose, a user needs to fix not only the cap. For instance, another row or even a couple of rows must be fixed when scrolling the document. Doing it is possible and easy, when you follow these steps:
- Select any cell under the line, which we will fix. This action will help Excel to “understand”, which area should be fixed;
- Now select the “Freeze Panes” tool.
When you perform horizontal and vertical scrolling, the cap and the top row of the table remain fixed. Thus, you can fix two, three, four and more rows.
Note: This method works for 2007 and 2010 Excel versions. In earlier versions the “Lock areas” tool is located in the “Window” menu on the main page. There you must always (!) activate the cell under the freeze row.
Freezing columns in Excel
For instance, the information in the table has a horizontal direction. It is not concentrated in columns, but located in rows. For his comfort, the user must freeze the first column, containing the lines’ names in horizontal scrolling.
- Select any cell of the chosen table so that Excel understands with what data it will work. Pin the first column in the menu you will see..
- Now, when the document is scrolled to the right horizontally, the needed column will be fixed.
To freeze several columns, select the cell at the page bottom (to the right from the fixed column). Pick the “Freeze Panes” button.
How to freeze the row and column in Excel
You have a task – to freeze the selected area, which contains two columns and two rows.
Make a cell at the intersection of the fixed rows and columns active. However, the cell must be not placed in the fixed area. It should have the position right under the required lines (to the right of the required columns). Pick the first option in Excel Locking Areas. The photo shows that when scrolling, the selected areas remain at the same place.
Removing a fixed area in Excel
After fixing a row or a column of the table the button deleting all pivot tables becomes available.
After clicking – «Unfreeze Panes», all the locked areas of the worksheet become unlocked.
'Data source reference is not valid' is one annoying problem that can be hard to get rid of, if you don't know the source problem. In this article, we will learn why data source isn't valid occurs and how we can solve this annoying problem.
So, as the name suggests the error occurs when Excel tries to refer to location that is no longer available, invalid or doesn't exist. Now this error can occur due to several reasons.
1: Pivot Tables Data Source Reference is Not Valid
So, when we create a pivot table it requires a data range. Using that data range excel creates the pivot reports. Now if you try to create pivot table with invalid range or refresh pivot table that refers to a range that no longer exists, this can cause 'Reference is not valid error'.
For example, here I have a data table, initially whose name was 'Table1' but I change it to 'data' later. Later I wanted to create a pivot table on this table.
So I went to insert --> Pivot tables. When it asked for the data source, I mentioned 'Table1'. This returns a 'Data source reference is not valid' error. Because I do not have any range called 'Table1' (I had changed it to 'Data').
So, this one of the main reasons for data reference invalid error.
How to solve Pivot Table Reference Error?
If you have several pivot tables in a workbook that refers to multiple ranges, named range or data tables, make sure that they refer to valid range. When you refresh pivot tables and this error occurs, it is possible that you have changed some range or names in the data mode. Check one by one each of them and save your excel file. This will solve reference error problem.
2: Named Ranges with #REF
The named ranges are great tools when it comes to managing structured data. But you got to be careful with the named ranges. When you name a given range, excel tags that specific name to the given range.
By default excel adjusts the named ranges when you delete or add rows to the named ranges. But if you delete entire range, the named range loses its reference. All the formulas, validations and condtional formatting depending on that range will not work. And if you try to copy that sheet or file you may get 'reference is not valid' error.
How to solve named range 'Reference invalid error'.
To solve this error, use the shortcut CTRL+F3 shortcut. This will open the name manger. Check the names and there ranges. Delete or fix all the named ranges that refers to contains #REF!. Save your file. This will remove the 'data source reference is not valid' error.
3: Table Name Change Caused Reference Not Valid Error.
Tables in Excel are great functionality. They help data management a lot. But they can cause the Reference errors to. It is similar to named ranges. When you use Table names in formulas, data validation, conditional formatting, and overtime when you delete or do some major changes to table, excel can through reference not valid error. This may not occur immediately but when you copy ranges or sheets to other workbooks or location.
How to avoid Table Caused Reference Error
To avoid such errors, you should check all the formulas, validations, conditional formatting that refers to table that has changed a lot earlier. Change or delete such formulas. This will solve the ' Data Reference not Valid Error'
4: Referencing to Invalid Data Source
Excel has features of importing data from other sources. All these features can be accessed from data tab. 'Get external Data', 'Power Query', and 'Power Pivot' are such examples. These features allow users to maintain data outside the Excel and import them whenever needed.
Now this feature is great but this cause problems when you accidentally or intentionally delete or move data source from the defined location. When the data source does not exist at the defined location, it is obvious that excel shows 'Data source reference is not valid'.
How to solve Data Source reference is not valid?
Check all the connections your file has. Check the connections to Access database if you have. Check it the location of the data source has changed. This is one of the main reasons of 'Data source is not valid' Error. Even if you change the name of the data source, it can cause the error. You need to change the name in Excel connections too.
To solve this error, go to Data --> Get External Data --> Existing Connection. A window will open that will show the existing connection to the workbook. Check the connections and if you don't need them anymore, remove them. If you do need them. Go to the location of the data source to check if that exist or not.
A lot of time, the connection is valid but file still throws a reference isn't valid when excel refreshes data. It is possible that the query or table that you are trying to access from the database is changed or does not exist anymore. In that case, you will need to open the data base and check the queries thoroughly. You will find the route cause of the error here surely.
5: Macro Using Undefined Reference
If the error 'excel reference isn't valid' occurs when you run a macro than there can be a lot of reasons.
The first reason can be that macro references to a range that no longer exist in system. This can be easily solved by clicking on the debug method.
Other reason can be the database connection. If you are trying to access database from VBA and trying to run a query with a invalid connection or query than you may get this error. So you have to do all the things mentioned in previous step.
Other reason can be that you used an object whose reference is not added. Like if you want use active data object, you will need to add reference to that object first.
To add a reference to an object, open VBE using Alt+F11 key. Now go to tools in menu, click on References. It will open a dialog box that has all the available resource. Search the object you need. Check it and click OK button. This will add the reference to that object.
So yeah guys, these are the reasons and methods to solve the invalid data source error in Excel. I hope it was helpful. If you are still facing the difficulty removing this error, let me know in the comments section below. Till then keep Excelling.
Related Articles:
13 Methods of How to Speed Up Excel | Excel is fast enough to calculate 6.6 million formulas in 1 second in Ideal conditions with normal configuration PC. But sometimes we observe excel files doing calculation slower than snails. There are many reasons behind this slower performance. If we can Identify them, we can make our formulas calculate faster.
Center Excel Sheet Horizontally and Vertically on Excel Page : Microsoft Excel allows you to align worksheet on a page, you can change margins, specify custom margins, or center the worksheet horizontally or vertically on the page. Page margins are the blank spaces between the worksheet data and the edges of the printed page
Split a Cell Diagonally in Microsoft Excel 2016 : To split cells diagonally we use the cell formatting and insert a diagonally dividing line into the cell. This separates the cells diagonally visually.
How do I Insert a Check Mark in Excel 2016 : To insert a checkmark in Excel Cell we use the symbols in Excel. Set the fonts to wingdings and use the formula Char(252) to get the symbol of a check mark.
How to disable Scroll Lock in Excel : Arrow keys in excel move your cell up, down, Left & Right. But this feature is only applicable when Scroll Lock in Excel is disabled. Scroll Lock in Excel is used to scroll up, down, left & right your worksheet not the cell. So this article will help you how to check scroll lock status and how to disable it?
What to do If Excel Break Links Not Working : When we work with several excel files and use formula to get the work done, we intentionally or unintentionally create links between different files. Normal formula links can be easily broken by using break links option.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
How to use Excel VLOOKUP Function| This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to use theExcel COUNTIF Function| Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.
Microsoft Excel Will Not Open
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.