This article is the ultimate guide to using Microsoft Excel like a pro!
With this guide, you will understand interesting and helpful tricks to execute simple and complex tasks with the help of shortcuts and tools.
Read till the end to start using Excel like a pro!
Pasting multiple texts at once
With Excel’s magical clipboard you do not have to worry about copying and pasting different texts one by one. You can copy all the text or values from different locations and paste all of them at once in the sheet.
Here are the steps to follow.
- Select and copy the texts or values from different locations like Word, Chrome, or PDFs.
- Press CTRL+C to copy.
- Open an Excel file.
- In the Home tab, click on the small icon in the bottom right corner in the Clipboard section.
- You can see the list of copied texts in this window.
- Click on a cell where you want to paste all these values.
- Press Paste All.
You can see that all the values copied from different locations are pasted at once.
Keyboard shortcut to create a chart quickly
To create a chart for tabular data you created within 30 seconds follow these steps below.
You should have the tabular data prepared in Excel for this.
- Select the entire table range.
- Press Alt+F1 or Alt+Fn+F1 on your keyboard.
You can see that a simple bar chart has been created for your table.
If you wish to change the chart type.
- Click on the chart.
- Go to the Chart Design tab.
- Click Change Chart Type.
- Select a chart type from the variety of chart categories.
To create a copy of your existing chart.
- Press and hold CTRL.
- Move the chart in the sheet and release the CTRL-key.
A copy of the chart gets created.
Displaying applied formulas
If you want to view all formulas applied in the sheet at once, follow these steps.
- Open the workbook with formulas.
- Press CTRL+~ on your keyboard.
You can see that the sheet is expanded and is displaying all the applied formulas in each cell. Press CTRL+~ again to hide the view.
Copying data from one sheet to another automatically
If you want to copy data from the time you generate it in a sheet to another sheet, follow these steps.
- Stay in the sheet where you will enter the data.
- Press and hold CTRL and click on the sheet where you want the data in the existing sheet to be copied.
You can see that the two sheets have been combined. You can select multiple sheets as per your preferences.
- Start typing in the sheet and you will see that it is being copied on the next sheet automatically.
- Press CTRL and click on the sheets again to ungroup them.
Quick Analysis tool
Most people are unaware of the Quick Analysis tool in Microsoft Excel. With this tool, you can add formatting, tables, sparklines, charts, and totals to your data in a jiffy.
- In the Formatting tab, you can add conditional formatting options like Data bars, Color Scales, Icon sets, Top 10%, etc.
- In the Charts tab, you can insert custom chart styles like a clustered bar chart, scatter plot, stacked bar charts, and more.
- In the Totals tab, you can apply formulas like sum, average, count, and more.
- You can add sparklines in the Sparklines tab in the Quick Analysis tool.
The Sort and Filter Tool
The filter tool can be used to filter large amounts of data systematically. You can filter data by color, ascending and descending orders, and a variety of custom number filters. The keyboard shortcut of the filter tool is CTRL+SHIFT+L.
The sort tool allows you to sort out data based on font color, cell values, cell colors, and conditional formatting icons.
Also check: Tips and Tricks Using the Advanced Filter Tool in Excel
Remove duplicate values
If your database contains duplicate values and you want to remove them, then follow the steps below.
- Select the entire data range.
- Go to the Data tab.
- Click on Remove Duplicates.
- Select the column names from which you want to remove duplicate values.
- Click OK.
Also check: [Easy] How to find and remove duplicates in Excel?
Conclusion
This article was all about the top tips and tricks in Microsoft Excel that will help you use Excel like a pro. Stay tuned for more tips like these on QuickExcel.