Restrict Worksheet Scrolling in Excel
There are over 1,000,000 rows and 16,000 plus columns in each Excel worksheet.
Most of the time our data fills very little of the available area and all that extra space can sometimes be a problem.
If less experienced users need to access your worksheet they can end up getting lost in the wide open spaces that sit outside the data area.
Or, you may want to restrict access to certain data outside the work area to keep it safe from accidental changes.
Whatever the reason, you can temporarily limit the number of rows and columns accessible by changing the Scroll Area property of the worksheet.
To find out how to do this read the short tutorial on how to Restrict Scrolling in an Excel Worksheet
Related TutorialsExcel Freeze Panes
Excel Freeze Panes Tutorial
© Ted French
Freeze panes lets you to lock specific rows and columns so that they will always be visible on screen no matter how far you scroll to the right or down.
Using this feature can prevent constant back and forth scrolling in order to check which column or row of data you are looking at in large worksheets.
To find out the details, read this tutorial on using Excel's Freeze Panes feature
Related Tutorials
View Multiple Copies of a Worksheet
View Multiple Copies of a Worksheet
© Ted French
To see more than one copy of a worksheet in Excel use the split box to divide the screen into panes.
Splitting the screen makes it easy to compare different rows or columns of data located in different parts of a worksheet.
It can also be used as an alternative to freeze panes for keeping worksheet titles or headings on screen as you scroll.
To find out all the details, read the tutorial on how to Using the Split Box in Excel.
More Worksheet TutorialsExcel Macro Tutorial
A macro is a recording of each command and action you perform to complete a task. Then, whenever you need to carry out that task in a spreadsheet, you just run the macro instead.
Complex macros are usually created in Excel with the Macro editor. Simpler macros can, however, be created using the Excel macro recorder.
To find out how to create a macro using the macro recorder in Excel, read the step by step Excel Macro Tutorial.
Related Tutorial
Excel DAVERAGE Function
Another of Excel's database functions is DAVERAGE.
Like the other functions in this group, DAVERAGE lets you specify one or more criteria which act as data filters .
Only those records that match the set criteria are included in DAVERAGE's calculations.
Once the data has been filtered, DAVERAGE will return the average value for a field of data chosen by you.
To find out all the details, read this tutorial on the Excel DAVERAGE function.
Related Tutorials
Excel DCOUNT Function
Excel DCOUNT Function
© Ted French
DCOUNT is one of Excel's database functions.
This group of functions makes it easy to find specific information in an Excel database file.
DCOUNT's job is to count up the number of cells containing data that meet one or more criteria set by you.
These criteria act as filters reducing the data processed by the function.
To find out all the details, read this tutorial on the Excel DCOUNT function.
Related Tutorials
INDEX - MATCH Lookup Formula
VLOOKUP is one of Excel's better known lookup functions.
It is limited, however, to using a single lookup criterion when trying to find information in a data table.
We can get around this limitation by using the INDEX and MATCH functions together to create a lookup formula that allows multiple criteria to be used when finding the data we are after.
So instead of finding items by name only, using multiple lookup criteria would allow you, for example, to find those with a certain name that were purchased from a specific supplier, or are a certain color.
To find out more read the step by step tutorial on Lookup Formula with Multiple Criteria in Excel.
Related Tutorials
Excel Two Way Lookup Formula
Excel Two Way Lookup Formula Tutorial
© Ted French
Although VLOOKUP is one of Excel's most used functions, on its own it is limited to using a single lookup criterion when trying to find information in a database.
By combining it with the MATCH function, however, we can create what is known as a two-way or two-dimensional lookup formula that allows us to easily cross reference two fields of information in a data table.
A two way lookup formula is useful when you want to find or compare results for a variety of different situations such as the price of different items over several months.
To find out all the details, read the step by step tutorial on the Excel Two Way Lookup Formula.
Related Tutorials
Excel SUM OFFSET Formula
The SUM function, by itself, will usually accommodate inserted cells of data with one exception - when the data is inserted into the cell where the function is currently located.
By using the SUM and OFFSET functions together, however, the range that is totaled becomes dynamic. Or, in other words, it changes to accommodate new cells of data. The addition of new cells of data does not cause problems because the range continues to adjust as each new cell is added.
Read this tutorial on the Excel SUM OFFSET Formula to find out all the details.
Related TutorialsExcel MATCH Function
Excel MATCH Function
© Ted French
The MATCH function, one of Excel's lookup functions, can be used to find the exact position of data in an Excel database or list.
For example, MATCH can be used to find how well a specific stock is performing in a list of investments or how well a student is doing relative to the class as a whole.
MATCH can also be combined with other functions, such as INDEX, to make more complex and versatile lookup formulas.
To find out all the details on how this function works, read this short tutorial on the Excel MATCH Function.
Related Tutorials