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.
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.
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.
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.
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.
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.
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.
You have some data and want to gain a quick overview? Or conduct some easy evaluation? Maybe later on analyze the data in more detail? For all these purposes, a Pivot Table can be a good choice.
Which step do you spend the most time, when you create an Excel model? Typing formulas and checking results, or rather setting the format, e.g. selecting borders, backgrounds, fonts, font sizes, font styles and even row and column heights and width? Formatting an Excel table can take a large amount of time, but “Format Painter” can greatly reduce the time you needed.
One advanced scenario of Conditional Formatting is to use formulas to determine the format. Let’s say, you want to change the background color of cell A if cell B has a certain value.