As a beginner, the process of creating excel spreadsheets, completing formulas and functions can be devastating. If you ever find yourself in a situation where you put data manually, you have missed a process that can be done automatically. Sometimes you don't have free time to watch tutorials. Nonetheless, you can master simple commands to work efficiently and avoid tedious and difficult work. Here are 59 excel tips and tricks that will help you to spend less time copying and pasting data.
1. Use pivot tables
Excel offers many features for any beginner to find useful. Pivot tables are an example of an excel tip or trick worth knowing. They are used to recognize, reorganize, group, and make sense of large data sets in a worksheet. The best thing about a pivot table is that you can summarize your data in a clear and precise manner without changing the original (raw) data.
Any beginner who knows their way around an Excel worksheet can use a pivot table when presented with data to summarize. To insert it, go to the Insert tab, and under the Table group, select pivot table. Automatically, excel will populate your pivot table with data from the active worksheet.
2. Use filters
Filters are a powerful Excel feature that enables a user to simplify their data. This feature comes in handy, especially when you have large data sets. It makes work easier, more so when you need particular data that fits into a criterion, you are working on.
From the numerous rows and columns available in an excel worksheet, you will be able to access any information. Luckily enough, excel allows you to use the filter option on every column. All you have to do is to right-click, and from the pop-up menu, you will be able to filter your column data. In case you want to use the feature to search for data in a whole worksheet, click on the Data tab and choose Filter; from the drop-down arrow next to your column, you can select the type of filters you want to be applied.
3. Use more than one row or column
At times working with excel data, you may find that you want your data in a particular order from row to row, column to column. At times you might miss certain entries and hence needed to add rows of columns. Adding a single row or column at a time can be tedious when you are expected to add many of them. To make your work simple, highlight the number of rows you want to be added, then right-click and select Insert.
4. Use the VLOOKUP function
The VLOOKUP function enables you to pull data from one sheet to another. It is particularly beneficial when working with data from two different spreadsheets that you want to be combined in one spreadsheet. It is a good function when dealing with lists in excel. For you to use this function, you are required to use the formula below;
=VLOOKUP (lookup value, table array, column index, [range lookup])
- Lookup value: The value you want to search for.
- The column index is the column number in the range that contains the value to retrieve.
- Range lookup is the return of an approximate value (exact match=false, optional=true)
5. Add checkboxes
An Excel checkbox is an interactive tool that is used to select or deselect an option. When dealing with data forms or customer data, checkboxes come in handy when you need quantifiable feedback.
To add a checkbox in excel, go to Developer Tab > Controls > Insert > Form Controls > Check Box. From here, click anywhere in the worksheet you need it to be, and it will insert a checkbox.
6. Hyperlink cells to a site
When using excel worksheets, you may want to keep track of websites or social media pages you find relatable to your data. That's why we add hyperlinks. To do this, you can add a URL link directly into your excel worksheet. Clicking on it will automatically redirect you to that website.
There are cases you may want to add a hyperlink to a title or specific data. To do so, highlight the data or title, click on the Data tab, and select Link. Otherwise, you can use the keyboard shortcut Shift K.
7. Discard duplicate data sets and points
When working with an excel worksheet that has large data sets, there are always possibilities of having duplicated content. In such a situation, knowing how to remove these duplicate data comes in handy.
To remove duplicates, highlight the column or row that has the duplicate data. Then go to the Data tab, under the Tools group, select Remove Duplicates. From the pop-up window that appears, check the boxes to confirm which data you want to work with. Lastly, click on Remove Duplicates to initiate the Command.
8. Create simple graphs
Graphs are a great way to summarize and simplify your data presentation in excel. You can easily create graphs from given data set values to communicate a summary projection to an audience.
To easily visualize your data and present it in an easy way to end-users, highlight the data you need to summarize. Go to the Insert Tab > select a simple graph to use.
9. Add header and footers
In this technological era, the need for paper has become scarce. Most excel users do not add headers and footers to their worksheets. At times, you may still need to print your excel worksheet. The best way to keep track of what you print out is by using the header/footer feature to add page numbers or timestamps.
Any new beginner in excel can add a header or footer by changing the layout of the worksheet.
- Go to the View tab
- Under the workbook views, select Page Layout.
- Next click on Insert tab > Text group > Header & Footer.
10. Group and ungroup columns
Excel worksheets may at times contain very detailed and complicated information. Sometimes it can be hard to read and analyze this information hence the need to know the group and ungroup feature in excel. The feature allows an excel user to collapse and expand all the complicated data for better understanding.
To do this, select all the data you want to be summarized. Go to the Data tab > subtotal (to open a pop-up window). Select how you want your data grouped and click OK.
11. Protect Workbook and sheets
When it comes to sharing your worksheet with different users, it is crucial to protect your work. When you protect your worksheet, it prevents other users from editing your data, altering any figures, or adding their information.
To protect your excel worksheet, click on the Review Tab > Protect Sheet. It will bring a pop-up window where you can add a password. Only external users who have your password will be able to edit it.
You can also protect your Workbook while saving the excel file. Go to Save As dialog box > Tools > General options. Add a password and click OK. Re-enter the password and click OK again, then save the file.
12. Use the drop-down list
Drop-down lists enable people to work more efficiently in excel worksheets. They can facilitate data entry more so when you want users to select items from a list without having to type in their values. To create an excel sheet, you need two things; a list of values contained within a cell range and a blank cell to use as the data entry cell.
Here is what you need to do when creating a drop-down list. Highlight the cells you want the drop-down list to be in, go to the Data tab > Validation (to open a validation settings box) > Allow options> Lists > Drop-down List. Check the In-Cell drop-down button, then OK.
13. Add Bullet points
Most times, Excel worksheets are primarily used for numbers. Despite this, you can also use text data such as bulletin boards and to-do lists. To make your steps easier to read and understand, it is always good to list them using bullet points.
Unluckily, excel does not offer a built-in feature for bullets like word documents. It should not deter you as you can still add the bullets in the following ways;
- Add bullets using the symbol menu.
- Insert bullet points using keyboard shortcuts.
- Use bullet points with excel formulas.
- Copy your bulleted list from Word.
14. Track important cells
Sometimes we may need to track all the important cells while working on excel worksheets. The best way to do this is by using a watch window. A watch window enables you to add important cells and get specific information relating to them in one place. It helps save on time that may otherwise be used to navigate to each important cell.
Here is what to do. Go to the Formula Tab > Formula Auditing > Watch window (to open a dialog box). Click on Add Watch, then select the cell you want to add and click OK.
15. Locate keyboard shortcut
It is easier to locate keyboard shortcuts when you are using Excel from 2007 to 2016. You can easily get ribbon keyboard shortcuts by pressing on the ALT key. Once pressed, it will show the shortcut keys for the options available in the main menu ribbon.
16. Use macros
Excel macros are beneficial when performing repetitive tasks as they help save on time. When you create a macro, it does not apply to every single spreadsheet you open by default. A macro is usually tied to the Workbook it was created in. despite this, it can be available all the time when saved as a Personal.XLSB file. You can also add your macros in the quick access toolbar. You can create macros through the VBA window or Developers tab.
17. Summarize data
It is not difficult to summarize excel data. You can easily summarize data using the status bar or the AutoSum option. To use the status bar, select all the values. The status bar responds by displaying a summary of the selected values. You can easily customize the status bar to display the exact information you need by right-clicking on it and choosing your preference.
18. Use styles and visuals
We use styles and visuals in excel to capture the full attention of the target audience. A bland work can get boring hence the need to use different styles and visuals. Excel offers this option which can be applied in table styles, sparklines, and data bars. These styles enable your data to practice speaking to someone. In case of styling a table, click on any cell in your data. Go to the Home tab > Format as a table (from the styles section). Select a style and click OK.
To visualize your table, highlight all the data you want to visualize. Go to the Home tab > Conditional formatting > Data Bars> select a style.
19. Build dynamic charts
A dynamic chart is a chart that updates automatically when you make changes to the data source. It is something that users or your audience can appreciate. To make this possible, start by defining your chart's source data as a dynamic range.
To do this, click the Insert Tab. Go to the Tables group and click table. Lastly, click OK. You can also build a dynamic chart using a formula =OFFSET.
20. Lower data input and typos
Any person who does data entry may at times encounter errors and wrong entries. Let's face it, and the process can be boring and tedious at times. To lower the risk of errors, you can use excel features to help reduce keystrokes. These features include the AutoCorrect Option, Special formatting, and auto decimal point.
21. Add tabs
Utilizing tabs in excel is a great way to stay organized in your projects. You separate the different elements of your data into various categories. With an open excel worksheet, you can easily add other tabs. You can access this at the bottom of your open excel sheet.
To create a new tab, click on the plus sign (+) next to your open worksheet 1. It will appear as a new blank sheet 2.
22. Sort data
Sorting data in large excel worksheets can be tedious and time-consuming. Lucky enough, excel makes this easier by offering its users the sort feature that can easily be accessed from the main menu ribbon. To do this, go to Data Tab, under the Sort & Filter group, select Sort.
In case you have data that you need to sort frequently, you can add this feature in the quick access toolbar to custom sort.
23. Merge cells
You can merge two or more cells that border one another in your excel spreadsheet to create one large cell.
Highlight all the cells to merge, then go to the home tab. In the alignment group, click on the arrow to open a dialog box. Lastly, check the box 'Merge cells' then OK. You can also easily do this by right-clicking after highlighting the cells you want to be merged.
24 Use formulas
Formulas are a must-know in excel as they allow for quick computing. There are many formulas to be used in excel, but as a beginner, you may only need to know the basic ones. The formula is typed in the fx bar. Note that all formulas should start with an equal sign followed by the cell name.
25 Format cells
Format Cells is a powerful tool in excel as it aids in readability, especially if you plan to print your worksheet. You can format your excel cells. The default cell type in an excel worksheet is 'General.'
To format a cell of your choice, here is what you have to do—Right-click on the cell and select Format Cell from the pop-up menu to display a dialog box. In the different tabs displayed, click on the Number tab. Here, selects the cell type to format your cell. Note, you can change many cells at once when you highlight them and then select the cell type from the drop-down menu. Apart from this, you can select adding a border to cells to format them.
Excel Autosum is used to sum a single range of cells by automatically creating a suitable SUM formula.
Follow the steps below to use Autosum in Excel:
- Select a cell that is immediately after the values you want to sum:
- To sum a row, select the cell that is after the last value in the row to the right.
- When summing the column, select the immediate cell after the last value in that column.
- Check and click the Autosum button on either the Home tab or the Formulas tab.
- The ranges of cells you are totaling are highlighted, and a sum formula appears in the selected cell.
- Complete the formula by clicking on the Enter key
- The total appears in the cell and the sum formula in the formula bar.
27. Lock cells
Below are steps to lock excel cells from being edited by other users.
- First, select all the cells to be locked.
- Second, right-click and then click on Format Cells. You can also work on this step by clicking on CTRL+1.
- Third, verify that all cells are locked by default in the Protection tab.
- Complete by clicking OK to lock the cells.
28. Save the file
It is important to save done files for reference and editing in case of any need. You first have to save your files before you can share them out. Below are steps to follow when saving the files.
- Click the File option in the Ribbon at the top.
- Click on Save As if you are saving the file for the first time.
- Under Save As, choose a location where you want to save your file. For instance, you can save on the desktop or one drive.
- Type the name of the file in the File name box.
- You can also save the file in a different format, such as .xls or .txt. Click on the Save As Type list To choose the file format.
- Finally, click on Save.
29. Open files in bulk
- Open the workbooks that contain the files to be opened.
- Click on Arrange All in the Window group under the View tab.
- Under Arrange All, select ton on your preferred option.
- Select the Windows of an active workbook in case all the files you want to open are in the active Workbook.
30. Delete blank spaces
- In the menu bar at the top, click on the Home tab and choose the Find & Select in the Editing group.
- Click on the Go To Special
- Select the Blanks option in the pop-up box that appears on the screen and click OK.
- On the Home tab, click Delete in the Cells group.
31. Hide data to avoid destruction
- Open your worksheets in Excel.
- Select the data you want to hide. You can either select a single row or multiple rows.
- Right-click anywhere within the selected data.
- Click on Hide from the several options displayed.
32. Start with 0 when inputting values
- Start by typing an apostrophe.
- Type the number intended by starting with a 0 after the apostrophe. Remember to add space within the number.
- Select the cells you want to input numbers that begin with zeros.
- Right-click on the selected area and choose the Format Cells in the list that appears.
- Under the Format Cells option, click on Numbers.
- Select the Text option under the Categories list that displays below Numbers.
- Click the OK button.
33. Start working from left to right
- Click on the File tab to open the Excel dialogue options by selecting Options.
- In the Excel options, click on the Advanced menu.
- Scroll down to the Display section and check under the Default direction for the Left-to-right radio button.
- Click the OK option below, and you will realize the direction change when you open a new worksheet.
34. Keep data in a single spreadsheet
- Right-click the spreadsheet name tab.
- Choose Move or Copy from the options displayed.
- Under the Move or Copy section, click on Move Selected Sheets To Book.
- Click on New Book the OK
- Click the File tab at the top, and then save.
35. Use custom files
- Change the display and print setting on a worksheet that you wish to save in custom view.
- Go to the View tab at the top
- Select Workbook views.
- Under Workbook views, click on Custom view, then Add.
- Type the name of the file in the Name box.
- Select the checkboxes of the settings that you want to include under Include in Views.
All the files added to the Workbook will appear in the Custom Views under Views.
36. Use multiple columns and rows
- Place the cursor under the second row in the worksheet.
- Press the left button of the mouse while the cursor is still in the second row.
- Drag the cursor down to the number of rows you wish to use while the left button is still pressed.
- Release the mouse button.
Repeat the same steps in the column section to select multiple columns.
37. Use autocorrect to change spelling mistakes
- Open the Workbook that needs to be checked.
- Open the Review tab in the Ribbon at the top.
- Click on the Spelling option.
- The errors in the document are evaluated, and suggestions are provided in the Spelling dialog.
- Click OK once done and save the work.
38. Activate watch window
- Click on the Formulas tab on the Ribbon at the top.
- Under the Formulas Auditing section, click on the Watch Window.
- An empty Watch Window dialog box will open.
- Click on the Add Watch at the top of the dialog box to add cells to the Watch Window.
- Select cells you want to monitor in the Add Watch dialog box. You can also select the cells by either using the mouse or typing directly the reference cells.
- Click the Add option after selecting the cells to be monitored.
39. Customize toolbar
- Click on Customize the Quick Access Toolbar at the topmost of the screen.
- Scroll down on the list and choose More Commands
- Under the More Commands section, select Commands Not in the Ribbon.
- Choose the Command from the list, then click Add.
40. Reduce calculation time
You can reduce calculation time in Excel through various ways, including Conversion of Formulas, as explained below.
- Choose the cells in which you want to convert formulas to values.
- Copy the cells by pressing Ctrl + C on the keyboard.
- Go to the Home tab and click the Paste option under it.
- Varied paste options will appear. Click on Paste Special.
- The formulas will instantly convert to values in the chosen cells.
41. Use defined names
- Choose the cells you want to name.
- On the Formulas tab, click the Define Name button in the Define names group.
- In the New Name dialog box, specify the Name box, set the name scope in the Scope drop-down, and check for the reference in the Refers to the box.
- Click OK to save the changes.
42. Use Sparkline micro charts
- Select the cells you want to place Sparkline in. Note that only one cell is used in each row or column.
- Click the Insert tab in the Ribbon at the top.
- Click on Sparkline and choose the type of Sparkline you need. They are of three types: Line, Column, and Win/Loss.
- After you have made a selection on the type of Sparkline, a Create Sparkline dialogue appears.
- The Create Sparkline fields are automatically filled in the selected cells.
- Click OK.
43. Transpose columns and width
- Open Excel and create a Blank workbook.
- Enter the data you intend to move from either a column to a row or from a row to a column.
- Select all the data entered in the worksheet, right-click and choose the Copy option.
- Click in a new blank space within the Sheet.
- Right-click in the blank space and choose the Paste Special option. You can also do this step by clicking Ctrl + Alt + V on the keyboard.
- Click on the Transpose option under the Paste Special box.
- Click OK to complete the Transpose process.
44. Use an excel camera to take screenshots
- Click on the Quick Access toolbar.
- Select More Commands in the displayed options.
- Under More Commands, choose Commands Not in the Ribbon.
- Click on Camera from the options that appear under the Command Not in the Ribbon section.
- Click on Add followed by OK to close the dialog box. The Camera icon appears in the Quick Access Toolbar at the top of the screen.
- To take a screenshot, select part of the worksheet that you need to take a photo of.
- Click on the Camera icon at the top of the screen, and it will automatically screenshot the selected datasheet.
45. Copy and paste data to another sheet automatically
- Open the source worksheet and select the cells with the data that needs to be copied.
- On the Home tab, click the Copy option.
- Go to the worksheet to be linked and click on the cells you want the data from the source to be copied to.
- Click on paste on the Home tab at the top.
- Several paste options display; select the Paste Link option under Other Paste Options.
- Save the work.
46. Flash fill
Flash fill happens automatically. It only requires a certain pattern to follow. Below are the steps to follow when using flash fill.
- Create a new column next to the column with your data.
- In the newly added column, type the desired value in the first cell.
- Excel senses the pattern and displays a preview of data to be flash fill in the remaining cells.
- Press Enter for the Preview to be done.
47. Merge tables
You can merge tables in Excel in different ways, such as the use of tools, use of power query, and use of merge tables wizard.
Below are steps to merge tables using the Power Query
- Go to the Data tab on the left top of your computer.
- Click on the Get Data option in the Get and Transform Data group section on the top right.
- Once you click on the Get Data option, several other options display. Select the From Other Sources option.
- From Other Sources options will display, select the Blank Query.
- In the Query editor, type the =Excel Current Workbook () formula in the formula bar.
- Click the enter key, and all the names in the worksheet will be displayed.
- In the name header, click on the drop-down icon to select the tables you want to combine.
- Click on the double-pointed arrows in the content header cell.
- Select the columns that need to be combined.
- Use original column name as Prefix option.
- Finalize by clicking OK.
48. Validate data
- Open the Data tab on the Ribbon at the top.
- In the Data Tools section, click on the Data Validation option.
- In the Data validation option, click on the Settings tab and define the validation criteria of your interest that include values, formulas, and cell references.
- Click OK once the validation rule is configured.
49. Text to column.
- Open Excel and create a new Blank workbook.
- Enter data in the first column and select all of them.
- Click on the Data tab in the Ribbon at the top.
- In the Data Tools section, select the Text to Columns option.
- Select on Delimited and click on Next.
- Clear all the boxes in the Delimiters section and select Comma and Spaces.
- Complete the process by clicking Finish.
- Open Microsoft Excel and create a new worksheet.
- Enter values in cell A1 and cell A2.
- Drag the fill handle down. Here the values get filled automatically in the selected cells. They follow the pattern used in the first two values to fill in the other selected cells.