Top 10 Excel tips

When you have multiple sheets in a workbook that all have the same essential layout (i.e., financial statements or invoices), you can use the 3D Sum to work with corresponding cells across different sheets. Create a new worksheet within your workbook, then pick a cell to type a formula such as = SUM(‘W1:W8’!A3), which will total up cell A3 from sheets labeled between W1 and W8. This helps when you want to create a master spreadsheet that tracks the data in your workbook as it evolves.

Because Excel is commonly used to store and analyze data, conditional formatting is one of our top Excel tricks as it allows you to quickly identify highlights from a large amount of data. Whether you’re identifying top give values or data highs and lows, conditional formatting can put a border around the highlights you’re seeking or even color code the entire grid. To enable, click the “Conditional formatting” drop down menu at the top of the document. You can use the Highlighted Cells Rules sub-menu to create more rules to look for more specific identifiers, such as a string of words, recurring dates, or repeating values.

When working on a spreadsheet that others will use, data validation is a way to keep things organized with a drop-down menu of selection to use so your colleagues can’t throw off the data set with an incorrect entry. To do so, highlight the cell where you’d like to place the drop-down, go to the “Data” tab and click “Data validation.” Tick “List” where it says “Allow:” and type the options you’d like to include, separated by commas, in the “Source:” field. To keep things extra clear, you can even create an error message other users will see if they try to enter data outside the desired range.

It’s common to wind up with a really complex workbook with tons of worksheets lined up in the tab at the bottom of the document. Excel allows you to hide sheets to keep the data and formulas available for other sheets, while keeping the overall document visually streamlined. Simply right-click the tab of the sheet at the bottom of the document, and select “Hide.” To bring it back, visit the “View” tab at the top of the document, select “Unhide,” then pick the name of the hidden tab from the list that pops up.