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.
PivotTables are an extremely helpful Excel feature that provides summaries of large collections of data. To create one for your data, check all of the columns and rows you’d like to include, then select “PivotTable” from the “Insert” tab. You can also use the “Recommended PivotTable” option to let Excel pick the right one for you based on your data, or add a PivotChart to your table that includes a chart to make the analysis easier to understand.
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.
When working across multiple workbooks, you’ll be very thankful for this Excel trick that allows you to hop between documents and avoid messing up an entire project by one mis-entry into the wrong workbook. Just hit Ctrl+Tab to quickly and efficiently shift between workbooks.
In some cases, you may need to add a diagonal line to the first cell of a section to separate different attributes of rows and columns. Click “Home,” then “Font,” then “Borders,” and you’ll be met with an array of border options to choose from. Click on through to “More Borders” for an additional menu of alternate options, click the diagonal line, then hit “Save.”
Though it seems like a swift action as it is, this Excel tip will help you to transport data around your document faster than ever. To move a whole column of data within a spreadsheet, choose the column, and move the cursor to its border. When the arrow turns into a crossed arrow icon, drag the column to move it wherever you’d like. To copy the data, press Ctrl before dragging to move and the new column will automatically copy all of the selected data.
It’s extremely easy to insert a screenshot from another program into your Excel spreadsheet. Go to the “Insert” tab, select “Screenshot,” and you’ll see a menu of thumbnail images from other open programs. Select the one you want, and resize or crop as you desire within the spreadsheet.
Saw this great paper being tweeted about this morning. Pretty short, but has virtually all of the everyone-can-do-this-but-they-don't tips and practices:HTML: http://www.tandfonline.com/doi/full/10.1080/00031305.2017.1375989PDF: http://www.tandfonline.com/doi/pdf/10.1080/00031305.2017.1375989?needAccess=true