Microsoft Excel is such a powerful tool that many people only scratched the surface of what it can do.
Excel is a great tool for data analysis and there are many ways to make your life easier when working with large amounts of data.
Here at The Excel Experts, we’ve handpicked some Excel tips and tricks to help you get the most out of this great software.
Use keyboard shortcuts
There are a ton of keyboard shortcuts in Excel that can save you time and make you more productive. A few of our favorites include:
Ctrl + C to copy
Ctrl + V to paste
Ctrl + X to cut
Ctrl + F to find
Check out this complete list of Excel keyboard shortcuts from Microsoft.
Customize your toolbar
The toolbar is the strip across the top of the Excel window that contains buttons for commonly used commands
You can customize which buttons appear on the toolbar by going to View > Toolbars > Customize. From here, you can add or remove toolbar buttons as you wish.
Use custom views
If you have a worksheet with a lot of data, it can be helpful to create a custom view. This will allow you to quickly switch between different configurations of your data without having to manually adjust things each time.
To create a custom view, go to View > Custom Views. From here, you can name your view and select which parts of the worksheet you want to include in the view.
You can then switch between views by going to View > Custom Views and selecting the desired view.
Filter your data
If you have a large dataset, filtering can be a great way to quickly find the data you’re looking for. There are two ways to filter data in Excel:
The first is by using the Filter command on the Data tab. This will add drop-down arrows next to each column header, allowing you to quickly filter by various criteria.
The second way to filter data is by using the AutoFilter command. This will add small icons next to each column header, allowing you to filter by criteria such as numbers, dates, or text.
Learn more about filtering data in Excel from Microsoft.
Use conditional formatting
Conditional formatting allows you to quickly visualize data that meets certain criteria. For example, you could use conditional formatting to highlight all cells that contain a number greater than 100.
To use conditional formatting, select the cells you want to format and then go to Format > Conditional Formatting. From here, you can choose from a variety of options to format your data.
Sort and filter by color
If you use conditional formatting, you can also sort and filter by color. This can be helpful if you want to quickly find all cells that meet certain criteria.
To sort by color, select the cells you want to sort and then go to Data > Sort. From here, you can choose to sort by cell color, font color, or fill color.
To filter by color, select the cells you want to filter and then go to Data > Filter. From here, you can choose to filter by cell color, font color, or fill color.
Use data validation
Data validation is a feature that allows you to control what data can be entered into a cell. For example, you could use data validation to ensure that only numbers are entered into a certain column.
To use data validation, select the cells you want to validate and then go to Data > Data Validation. From here, you can choose the type of data that can be entered into the cells.
You can also use data validation to create drop-down lists in Excel. This can be helpful if you want to make sure users select from a list of predetermined values.
To learn more about using data validation in Excel, check out this tutorial from Microsoft.
Use pivot tables
Pivot tables are a great way to summarize data. They allow you to quickly see how your data is distributed and spot trends.
To create a pivot table, select the cells you want to include in the table and then go to Data > Pivot Table. From here, you can choose how you want to summarise your data.
You can also use pivot tables to create charts. To do this, select the cells you want to include in the chart and then go to Insert > PivotChart.
Use goal seek
Goal seek is a feature that allows you to find the input value that produces the desired output value. For example, you could use goal seek to find the interest rate that would produce a given monthly payment on a loan.
To use goal seek, select the cell you want to find the input for and then go to Data > What-If Analysis > Goal Seek. From here, you can specify the target value and what cell contains the input value.
Use solver
Solver is a feature that allows you to find the optimal solution to a problem. For example, you could use solver to find the combination of products that would maximize profits.
To use solver, go to Data > What-If Analysis > Solver. From here, you can specify the objective function and the constraints.
Use the analysis toolpak
The analysis toolpak is an add-in for Excel that provides a variety of statistical and financial functions. To use the analysis toolpak, go to Data > Data Analysis.
Some of the most popular functions in the analysis toolpak are:
Anova: Allows you to compare multiple means.
Regression: Allows you to find the relationship between two variables
Correlation: Allows you to measure the strength of the relationship between two variables
Fourier Analysis: Allows you to find periodic trends in data
To learn more about using the analysis toolpak, check out this tutorial from Microsoft.
Use formulas and functions
Excel has a variety of formulas and functions that you can use to work with your data. Some of the most popular formulas and functions are:
SUM: Adds a series of numbers
AVERAGE: Calculates the mean of a series of numbers
COUNT: Counts the number of cells that contain a certain value
MAX: Finds the maximum value in a series of numbers
MIN: Finds the minimum value in a series of numbers
ROUND: Rounds a number to a specified number of decimal places
To learn more about using formulas and functions in Excel, check out this tutorial from Microsoft.
Add An Extra Line Of Text
Pressing ALT + ENTER will allows you to easily add an extra line of text inside a cell.
This can be helpful if you want to add a comment or extra piece of information.
Auto-size Columns
When you double click on the top of a column seperator, it auto-sizes the column automatically to fit your data.
Interchangeable Columns & Rows
Rich click on the column ‘copy’ the cells. Click where you need your first row or column to start and choose ‘paste special’ to find transpose.
Move Between Files
A little tip to move between files is to press CTRL + TAB. Not many people know about this Excel trick!
Delete Redundant Blank Cells
Select a column, go top Data > Filter and select ‘blanks’ which should show them all up with a single click so you can delete directly to remove all your blanks!
Highlight Multiple Cells
You can hold down CTRL while you are selecting cells to highlight multiple cells at a time. You can also use the SHIFT key to select a range of cells.
Insert Multiple Rows
To insert multiple rows, first select the number of rows you want to insert. Then, right-click on the selection and choose Insert.
Use Flash Fill
Flash fill is a feature in Excel that allows you to automatically fill in cells based on patterns. For example, if you have a column of first names and a column of last names, you can use flash fill to create a column of full names.
To use flash fill, start by entering the data you want to fill in the cells . Then, in the next cell, type the data you want to fill and press Enter. Excel will automatically fill in the cells for you.
To learn more about using flash fill, check out this tutorial from Microsoft.
Hide Columns & Rows
You can hide columns and rows by selecting them and then going to Format > Hide . This can be helpful if you want to clean up your spreadsheet or make it more readable.
Unhide Columns & Rows
To unhide columns and rows, go to Format > Unhide. Then, select the column or row you want to unhide.
Insert Subtotals
Subtotals are a great way to quickly summarize data in a spreadsheet. To insert subtotals, select the data you want to summarize and go to Data > Subtotal.
In the Subtotal dialog box, select the column you want to subtotal and choose the function you want to use (e.g., SUM, COUNT, AVERAGE). Then, click OK.
Excel will insert subtotals for the selected column. To view the subtotals, click the plus sign next to the row. To hide the subtotals, click the minus sign.
Drop Down Lists
Drop-down lists are a great way to limit the data that can be entered in a cell. To insert a drop-down list, select the cell you want to insert the list in and go to Data > Validation.
In the Data Validation dialog box, choose the settings you want for your drop-down list. Then, click OK. You can now select the cell and choose from the list of options you created.
Cell References
Cell references are a way of referring to a cell or range of cells in a spreadsheet. There are two types of cell references: relative and absolute.
Relative cell references change when you copy and paste them. For example, if you have a relative reference to cell A1 and you copy it to cell B2, the reference will change to B1.
Absolute cell references don’t change when you copy and paste them. For example, if you have an absolute reference to cell A1 and you copy it to cell B2, the reference will still be A1.
You can create an absolute reference by using the dollar sign ($) before the column letter and row number (e.g., $A$1).
You can also create a mixed reference, which is a combination of absolute and relative references. Mixed references use the dollar sign ($) before the part of the reference that should stay the same when you copy and paste (e.g., A$1 or $A1).
Duplicate Values
If you want to find duplicate values in a column, you can use the COUNTIF function. For example, if you want to find all the duplicate values in column A, you can use the following formula:
=COUNTIF(A:A,A1)>1
This formula will count all the cells in column A that have the same value as cell A1. If the count is greater than 1
This formula will count the number of cells in column A that have the same value as cell A1. If the number is greater than 1, it means there is a duplicate value.
You can also use the COUNTIF function to find unique values in a column. For example, if you want to find all the unique values in column A, you can use the following formula:
=COUNTIF(A:A,A1)=1
This formula will count the number of cells in column A that have the same value as cell A1. If the number is equal to 1, it means the value is unique.
You can use the COUNTIF function with other functions, like SUM and AVERAGE
=SUM(COUNTIF(A:A,A1:A10))
This formula will sum up the number of cells in column A that have the same value as cells A1 to A10.
Final Thoughts
Those are just a few of the things you can do with Excel. Once you get the hang of it, you’ll be able to do even more.
I hope you found these Excel tips and tricks helpful! If you do need help with any Excel related issues, please do get in touch with us.
