A troublesome topic when it comes to creating large Excel models is Error Checking. Excel offers an Error Checking function in the Formulas ribbon which let’s you jump to the next error. Error in this case are all the cells showing an Excel error message as #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!.
Knowledge Area
LEFT or RIGHT: Extract Text from Cells in Excel
LEFT and RIGHT are string formulas for working on text. You use them when you want to extract some parts of a text. LEFT returns a number of characters of a cell, starting from the left hand side. RIGHT does exactly the same, but starts from the right hand side.
How to Freeze the Headings
Your worksheet is larger than your screen? That means when you scroll up and down, the header row of your data is invisible?
How to Paste Links to Source Cells Instead of Values
You finished your calculations and now you are about to present your results? Well built Excel models usually separate the calculations from the results. Therefore “Paste as Link” might be helpful for you, especially when your colleagues should not mess your data source.
Buttons in an Excel Cell: How to Add a Form Control
You are looking for a simply way to make your Excel table look professional? Hardly known but easy to use: Buttons, for example, Check Boxes or Spin Buttons can change values. Before you start using buttons, you have to display the Developer tools. Right-click on any ribbon and click “Customize the Ribbon”. Make sure the box for Developer is ticked on the right hand side.
How to Set Headers and Footers When Printing: Two Methods!
Formatting an Excel table for printing is usually troublesome. You have to adapt the print area, select rows and columns to repeat and set the header and footer. In this Excel Tip, we’ll have a look at setting headers and footers.
Data Table: Analyze Your Results in Excel
An advanced method of analyzing the results of your calculations is to use a Data Table. With the Data Table, Excel can do your calculation again by changing two input values.
How To Hide And Group Rows And Columns In Excel
You got an Excel table with some unimportant rows, but you don’t want to delete them. In such case, you might want to “hide” them. There are two options of hiding rows (and columns): Either right-click on the row (or column) number and click on “Hide” or use the group function.
Remove Duplicates: How to Condense Excel Tables to Unique Items
Now we are coming to a very useful but rather unknown function: Remove Duplicates. Let’s say, you got a list of names and some names are twice on the list. With Remove Duplicates, you can delete those duplicated names automatically so that each name appears only one time.
How to Select All Blank Cells in Excel
When you work with data, you often want to select certain cells, for example all blank cells. You might want to fill them or just mark them.