Excel tips
Input Restriction
Similar to the data validation feature, there’s another way to restrict input values and offer guidance to other users adding to your spreadsheet. To ensure data outside of a given range is barred from your sheet, go to “Data,” then “Data Validation,” then “Setting,” and input your desired conditions. You can also shift to “Input message” and provide prompts that steer users to the correct data range, which will show up when their cursor hovers over the cell and trigger a warning message if the value is unqualified.
Excel Data To Word
Another easy but helpful Excel trick is copying cells or charts into Word. Though a straightforward copy-and-paste maneuver, be sure to note that this “link-and-embed” process — meaning that if you change the data in the spreadsheet — will also change the data in its Word counterpart. If you’d like to avoid this, instead paste the data as a graphic using Word’s “Paste special” feature. You can also select “Copy as picture” within Excel, and paste the static graphic into Word.
Screenshot Insertion
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.
Copy And Paste Quickly
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.
Add More Than One New Row Or Column
As you build and add to your spreadsheet, it’s essential to be able to add multiple rows or columns at once rather than going the manual route. To start, pick the area where you want to add a new batch of rows or columns, then click and drag the highlight over the number of lines you want to add (e.g., highlight seven rows to add seven). Right-click and choose “Insert” from the drop down menu to insert rows above the row or to the left of the column you selected.
Add Diagonal Line
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.”
Hide A Sheet
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.
Data Validation
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.
Conditional Format
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.
Pivot!
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.