Spreadsheets are one of the most basic yet versatile tools in all of information technology. They can be used to solve basic math problems, keep track of funds, organize lists and so much more. As with any informational technology though there are a lot of features people fail to use. Here are some spreadsheet tricks that every user should know.
1. Convert text to columns
If you have a text list with commas or spaces between the data, you can convert it into an Excel spreadsheet very easily. Simply select the text and go to “Data” > “Text to Columns”. A wizard will pop up and guide you through the conversion process.
2. Freeze panes
If you want to keep specific rows or columns in view while you scroll through the spreadsheet, use the freeze panes function. To do this, click on the row number or column letter at the top of the frozen column or row.
3. Create a dropdown list
You can create a dropdown list in a cell by completing following steps: 1) Select the cell and click Data > Data Validation. 2) Choose the settings that suit your spreadsheet and select “OK”. 3) Now, when you highlight the cell, a small dropdown arrow will appear to the right of the cell.
4. Easily find information
If you are looking for spreadsheet data fast, use Excel’s “Flash Fill” feature. Flash fill works by analyzing data in other cells-you select what column you want it to look at and then type in your desired column header.
For example, if you wanted to create a list of product codes from an inventory spreadsheet, simply put in two or three examples into one column and let Excel do its magic! Depending on how big of a file it is, you may want to consider something more specific to the date you’re working with — mrp software for inventory or a CRM for a prospect list. You can check their site anytime.But until then, this should do the trick.
5. Sum a column or row
If you want to quickly sum up all the numbers in a column or row, use the SUM function. Start by selecting the cell that will contain the total and then type “=SUM(“. After that, highlight the cells you want to include in the sum and hit enter. Ta-da- your sum will appear!
6. Hide cells from view
If you have sensitive data that you don’t want others to see, you can easily hide it from view. To do this, select the cells you want to hide and go to Format > Conditional Formatting > Hide Cells. A dialog box will pop up and ask you to choose a condition for hiding the cells.
7. Insert a hyperlink
If you want to make a spreadsheet more interactive, you can easily insert hyperlinks into cells. Simply select the cell and go to Insert > Hyperlink. A dialog box will pop up and ask for the URL of the website or document you want to link to.
8. Sort data quickly
If you need to sort your spreadsheet data quickly, use one of Excel’s many sorting options. To do this, select the cells you want to sort and go to Data > Sort. A dialog box will pop up and let you choose how you want to sort your data.
9. Convert numbers to text
If you have a column of numbers that you need to turn into text, you can use the TEXT function. To do this, select the cell that will contain the text and type “=TEXT(“. After that, highlight the cells you want to include in the conversion and hit enter. Excel will automatically convert them into text!
9. Create a chart
Excel makes it easy to create charts based on your spreadsheet data. Simply select the cells you want to include in the chart and go to Insert > Chart. A dialog box will pop up and let you choose the type of chart you want to create.
10. Format cells quickly
If you need to quickly format a cell’s contents or appearance, use Excel’s shortcut keys. For example, to make text bold, simply press Ctrl + B. You can also use shortcuts to change the spreadsheet’s font, alignment, size and color easily.
11. Format data based on another cell
If you need spreadsheet data to always be displayed in thousands or millions, for example, you can format it based on another cell’s contents. To do this, select the cells that will contain your formatting instructions and go to Data > Format > More Cells. A dialog box will pop up with various formatting options-simply select the one you need!
12. Reorder columns & rows
Excel makes it easy to reorder spreadsheet columns and rows by using simple drag-and-drop techniques. First click on the column or row header of the item you would like to move (it will turn into a four-headed arrow) and then drag it to the new location.
13. Freeze panes
If you have a large spreadsheet and want to keep certain columns or rows in view while scrolling, you can use Excel’s freeze panes feature. To do this, select the cells you want to freeze and go to View > Freeze Panes. A check mark will appear next to the menu item to indicate that the panes are frozen.
14. Protect your spreadsheet
If you want to protect your spreadsheet from being edited or accidentally deleted, you can use Excel’s protection features. To do this, select the cells you want to protect and go to Tools > Protection > Protect Sheet. A dialog box will pop up and let you choose the level of protection you want to apply.
These are just a few of the many tricks that Excel has up its sleeve. Once you learn these basics, you’ll be able to tackle any spreadsheet with ease! For more tips and tricks, check out Excel’s Help menu or search the web for more information.
