Table of Contents
Microsoft Excel is a spreadsheet application that offers an extensive suite of features, including tools for data storage, calculation, and Visual Basic programming. Excel is recognized as an industry standard for spreadsheet applications, with millions of diverse users worldwide. As a versatile, feature-rich application, Excel provides a variety of tools and features that are specifically beneficial for academic purposes. The list of tips and tricks below provides solutions to common challenges with formulas and calculations that you may encounter when using Excel.
For more tips and tricks on Excel view the Overview of Basics Tasks and the Excel Ribbon and Working with Cells, Rows, and Columns articles.
Apply formulas
Excel allows you to use formulas to calculate and analyze data that has been entered into a workbook. A formula is an expression that can be used to calculate the value of a cell. Excel contains a list of predefined formulas, known as Functions, which are available with most versions of the application. Formulas can be applied to one or multiple cells. When applied to multiple cells, formulas must be either moved or copied from the cell where the formula was originally applied.
Formulas can be copied by copying and pasting the formula from its original placement to another cell, or formulas can be moved by cutting and pasting the formula from the initial cell where it was applied to another cell. These commands can be found in the Clipboard section of the Home tab in the Ribbon.
Formulas can also be copied down a column using the fill handle (+) icon found in the lower right corner of a cell. This allows you to drag the formula down a column and apply it to other cells within a column.
Use a Calculated Column
An easy way to use formulas in a worksheet is to apply a formula before entering data. Formulas can be applied to single or multiple cells before entering any data, but it may be most useful to apply a formula to an entire column in anticipation of entering large data sets. When a formula is applied to an empty column before data is entered, a calculated column is created. Calculated columns can be used to mitigate errors that may occur from copying and pasting data, and these columns adapt to the addition and removal of rows.
Replace a formula with a value
When working with formulas, cells that contain formulas often display the formula rather than the result, or value, of the formula. Single cells or a range of cells that contain or utilize formulas can be converted to the calculated value(s) in order to present data that may be more accessible when viewed as a value rather than a formula. This is especially useful if the workbook contains multiple complex formulas and large data sets. Additionally, you can choose to only convert part of a formula into a value and freeze the remaining part of the formula.
To learn how to replace a formula with a value in a cell, view the Replace a formula with its result article in the Microsoft Support Center.
Display all worksheet formulas
Formulas that have been applied to cells in a worksheet can be displayed or hidden according to your preferences. As mentioned previously, formulas applied to a cell or range of cells can be replaced with a value(s). They can also be prevented from being displayed in the formula bar depending on the selected protection parameters for cells that are impacted by the formula. Toggling display preferences for formulas can be useful when working with multiple formulas, complex formulas, or large data sets. Adjusting display preferences can also be useful when attempting to maintain the security of data that has been entered into a worksheet.
To learn how to control the display of formulas, view the Display or hide formulas article in the Microsoft Support Center.