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 WorksheetRelated Tutorials
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
To see more than one copy of a worksheet in Excel use the split box to divide the screen into panes.
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 Tutorials
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.
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.
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.
It is limited, however, to using a single lookup criterion when trying to find information in a data table.
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.
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.
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 Tutorials
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.