Choose whether to clean only the active worksheet or all worksheets.On the Inquire tab, click Clean Excess Cell Formatting.To remove the excess formatting in the current worksheet, do the following: In the COM Add-Ins box, check Inquire, and then click OK. The Inquire tab should now be visible in the ribbon.Make sure COM Add-ins is selected in the Manage box, and click Go.If you don't see the Inquire tab in Excel, do the following to enable the Inquire add-in:
#How to clear formatting in excel 2013 plus
Microsoft has its own add-on called Clean Excess Cell Formatting, which is available on Excel's Inquire tab in Microsoft Office 365 and Office Professional Plus 2013.
![how to clear formatting in excel 2013 how to clear formatting in excel 2013](https://cdn.extendoffice.com/images/stories/doc-excel/clear-delete-table-format/doc-clear-delete-table-format-01.png)
Remove Excessive Formattingįormatting cells on your worksheet can make the right information easy to see at a glance, but formatting cells that aren't being used (especially entire rows and columns) can cause your workbook's file size to grow quickly. No matter it's a small or big Excel file since it causes a crashing issue in Excel, the given solutions in each section should help. Next, we're going to troubleshoot a slow workbook upon opening or editing in Excel around the discussed three factors. We suggest everyone turn on auto save in Excel, to save the workbook every 1~5 minutes. Conditional formatting with GET. Important A large excel file usually contains hundreds of rows of critical data with many styles and formulas in it, so you must take good care of the workbook by making a real-time backup, in case one day unexpected data loss happens due to the Excel crash, not responding or stop working suddenly before you can click Save. Three ways to find and highlight formulas:ģ. The next time you inherit a new workbook, try one of these 3 methods to quickly and easily find all formulas. To stop highlighting formulas, simply delete the rule. One advantage to using Conditional Formatting is that the actual formatting of the cells is not affected. If we add a new formula, we'll see it highlighted too. Because the highlighting is applied with Conditional Formatting, it's fully dynamic. Once we set the format, we'll see it applied to the cells that contain formulas. We want to use a formula to control formatting, and the formula we use is simply our named formula "CellHasFormula." Now we can select the range we want to work with and create a new Conditional Formatting rule. In this case, "rc" means current row and column, and FALSE tells INDIRECT that we are using R1C1 style references instead of A1 style references. The second parameter is the INDIRECT function. The first argument-48-tells GET.CELL to return TRUE if a cell contains a formula. For the reference, we use the GET.CELL function like so: However, by using a named formula, we can work around this problem.įirst, we create a new name called CellHasFormula. Unfortunately, GET.CELL can't be used directly in a worksheet.
![how to clear formatting in excel 2013 how to clear formatting in excel 2013](https://i.ytimg.com/vi/0P6qZRclAT0/maxresdefault.jpg)
Instead, we'll use a function called GET.CELL(), which is part of the XLM Macro language that preceded VBA. Excel 2013 includes a new formula called ISFORMULA(), which makes for a very simple Conditional Formatting rule, but that won't work in older versions of Excel. We can just reverse the process.Ī third way to visually highlight formulas is to use Conditional Formatting. Now all cells that contain formulas are marked for easy reference. For example, let's add a light yellow fill. With all formulas selected, you can easily apply formatting. In the status bar, we can see the number of cells selected. Excel will select all cells that contain formulas. In the Go To dialog box, click Special, select Formulas, and then click OK.
#How to clear formatting in excel 2013 mac
This works on both Windows and Mac platforms. The fastest way to open this dialog box is to use the keyboard shortcut Control-G. Go To Special is based on the Go To Dialog. The next way you can find all formulas is to use Go To Special. Using this shortcut, you can quickly and easily switch back and forth. This key is just below the Escape key on US keyboards. This shortcut will cause Excel to display the formulas themselves instead of their results. Of course, you can just start selecting cells, while watching the formula bar, but there are several faster ways to find all formulas at once.įirst, you can toggle the visibility of formulas on or off using the keyboard shortcut Control + Grave Accent. When you first open a worksheet you didn't create yourself, it may not be clear exactly where the formulas are. Knowing where formulas are is the first step in understanding how a spreadsheet works. In this video, we're going to look at three ways to find formulas in a worksheet.