87 Excel Tips And Tricks: From Beginners To Pros
- by Tech Today News
- Posted on November 28, 2023
Microsoft Excel was first released in 1985, and the spreadsheet program has remained popular through the years. You can master Excel by reading these tips and tricks on how to add a dropdown list in an Excel cell to find duplicates, how to delete blank rows in Excel and more. SEE: Microsoft Excel: Become an expert with this online training Jump to: How to add a drop-down list to an Excel cell How to add a condition to a drop down list in Excel How to add color to a dropdown list in Excel How to create an Excel drop down list from another tab How to use UNIQUE() to populate a dropdown in Microsoft Excel How to use VBA procedures to generate a list of sheet names in an Excel workbook How to add a timestamp to an Excel record How to send a scheduled Microsoft Excel report email using Power Automate How to convert a birth date to an age without an expression using Microsoft Excel Power Query How to filter a pivot table in Excel How to add conditional formatting to a Microsoft Excel PivotTable without expressions 4 tips for refreshing Excel PivotTable objects How to combine Excel VLOOKUP() and PivotTable for simple solutions How to display the top n records in an Excel PivotTable using built-in options How to convert a Microsoft Excel slicer into a series of filtering buttons on a PivotChart How to conditionally return the last value in a column in Excel A quick way to delete blank rows in Excel Six ways to remove blank rows from an Excel worksheet (free PDF) Copy an Excel sheet from one workbook to another How to average unique values in Excel the easy way How to parse data in Microsoft Excel How to Extract Delimited Data Using Excel Power Query How to limit the columns in a filtered result set in Microsoft Excel How to easily print Excel sheets in black and white How to create a fun people chart in Excel How to force a consistent phone number format in Microsoft Excel How to use Excel styles efficiently How to create a burndown chart in Microsoft Excel 2 ways to display negative numbers in red in Microsoft Excel How to use a border to discern groups more easily in Microsoft Excel How to create a static view of Excel data while collaborating How to suppress 0 values in an Excel chart How to Adjust Text to Fit in Excel Cells With 3 Methods How to use Excel’s find feature to highlight or delete matching values How to use Find All to manipulate specific matching values in Excel How to use EOMONTH() to return the last day of the month and more in Excel How to turn complex formulas into easy-to-use custom functions using LAMBDA() in Excel How to parse time values in Microsoft Excel How to split a column using an IF() function in Excel How to combine Excel’s VLOOKUP() function with a combo box for enhanced searching How to use the newish XLOOKUP() dynamic array function in Excel How to use UNIQUE() to populate a dropdown in Microsoft Excel 4 ways to multiply in Microsoft Excel How to enter and display fractions in Excel How to create a floating bar chart in Excel How to calculate a conditional running total using a PivotTable in Excel How to calculate conditional rank in Excel How to subtotal transactions by conditional date components in Excel How to do more advanced averaging in Excel How to average with and without the highest and lowest values in Excel How to calculate bonuses and commissions in Excel How to find the minimum and maximum values within a specified set of years in Excel How to copy expressions without changing cell references in Excel How to handle VBA’s four most common errors in Microsoft 365 apps How to highlight unique values in Excel 3 ways to suppress zero in Excel Use Excel to calculate the hours worked for any shift Four ways to protect your Excel formulas (free PDF) How to determine the number of remaining workdays for Microsoft Excel projects How to parse data in Microsoft Excel How to Extract Delimited Data Using Excel Power Query How to use conditional formatting to highlight due dates in Excel How to use Excel’s conditional formatting to make larger values more readable How to add a conditional format that highlights groups in Excel How to use the selected value in a combo to determine conditional formatting in Excel How to highlight the top n values in a Microsoft Excel sheet How to avoid a conditional formatting rule in Excel that doesn’t work as expected How to enter data quickly in Excel How to quickly import a .txt or .csv file into Microsoft Excel How to use hyperlinks to move quickly between sheets in Microsoft Excel 5 ways to rename a sheet in Microsoft Excel How to extract the date and time from a serial date in Excel 6 shortcuts for working with Table objects in Excel How to use Find All to manipulate specific matching values in Excel 6 ways to save time using Flash Fill in Microsoft Excel How to use sheet view for more flexible collaboration in Excel How to use shortcuts to sort in Microsoft Excel How to use passwords to grant users access to different Microsoft Excel workbook ranges How to transfer data from Word forms to an Excel worksheet How to find duplicates in Excel How to fix common printing problems in Microsoft Excel How to highlight details for better insight with sparkline charts in Excel How to turn ordinary sparklines into meaningful information with a few simple formats 10 handy ways to get more from Excel (free PDF) How to transfer data from Word forms to an Excel worksheet How to use Excel’s what-if tools to analyze business scenarios (free PDF) These new Excel features for working with text and lists will save you timeHow to use Excel dropdown lists
Drop-down lists can greatly facilitate data entry. Here’s a look at how to use Excel’s data validation feature to create handy lists within your worksheets.
Learn this quick and easy method for adding a condition to a data validation list in Excel.
This tutorial shows the steps for adding color to a dropdown list in Microsoft Excel.
Here’s a quick tip for creating a Microsoft Excel drop down list from another tab.
Populating a dropdown with a dynamic list is easy thanks to Microsoft Excel’s UNIQUE() dynamic array function.How to use macros in Excel
Use one or both of these simple Microsoft Excel macros to list all the sheets in an Excel workbook.
Like many Excel tasks, there’s an easy way and a better way to enter a timestamp for your records. Learn about both in this article.
If the boss wants to review a Microsoft Excel report every day at the same time, don’t worry: You can use a Power Automate flow to automatically send that report on time.
In Microsoft Excel, you don’t need an expression to calculate someone’s age — use Power Query to simplify the process.How to use Excel pivot tables
Pivot tables generate great reports in Microsoft Excel, but adding a filter or two can make them even more flexible. Here’s how.
Learn how to combine a pivot table with a slicer and conditional formatting to provide on-the-fly highlights.
Up-to-date information can be critical; these 4 tips will add flexibility and increase efficiency when refreshing pivot tables.
Combining features often extends the flexibility and efficiency of your solution. In this article, we used the VLOOKUP() function to add an important detail to a tracking list.
To display specific values in a PivotTable in Microsoft Excel, use one of the many built-in filters, such as Top 10.
Slicers are a good way to filter data in a Microsoft Excel PivotTable or PivotChart, but they take up a bit of room. By moving the buttons to the chart, you free up room for more visuals.How to manipulate data in Excel
Returning the last value based on a condition seems like a difficult problem to solve unless you try one of these two easy-to-implement solutions.
Deleting blank rows in an Excel data range is easy with this technique, but watch out for unintended consequences.
Blank rows can find their way into your worksheets through various means — but no matter how they get there, it’s a good idea to get rid of them. This e-book walks through five manual techniques for deleting blank rows and then winds up with a macro-based approach.
Susan Harkins shows you two quick ways to copy data from one Excel workbook to another.
If you need to average a list of values in Microsoft Excel that contain duplicates without including the duplicates, don’t fret over a complex expression when you can easily remove those duplicates from the calculation.
Simplify your data transformation by skipping complex parsing expressions and instead using Flash Fill and Power Query in Microsoft Excel.
Learn how to use Excel Power Query’s extract and split column features to extract delimited strings into their components with this step-by-step tutorial.How to use styles and visuals in Excel
Microsoft Excel’s Advanced Filter feature displays records that match specific criteria. You can also limit the columns returned by this feature.
Printing a colorized Excel spreadsheet in black and white is easy, whether you do so manually or use a short VBA procedure. Here’s how.
Is your chart boring? Try Excel’s people chart to liven things up. Susan Harkins shows you how.
Combine an Excel custom format with data validation and remove the pressure for input perfection from personnel by letting Excel do the work.
Learn what styles are and how they can help you format your sheets more effectively and efficiently.
Try this easy-to-implement Microsoft Excel chart to keep you and your team members on track.
Learn how to use either of these methods in Microsoft Excel to display negative values in red.
If your data is grouped in Microsoft Excel, try displaying a border between groups. Learn how to make your Excel tables easier to read.
When collaborating in Excel, sometimes the collaboration can be distracting. Here’s how to make a static image so you can do your own work without interruption.
There isn’t a one-size-fits-all solution for removing 0 values from an Excel chart. Here’s a look at a few methods.
Trying to make all of the text or words fit properly in your Excel spreadsheet? Learn how to make text fit in Excel with our tutorial.
Excel’s Find feature offers more than just finding values if you know the right steps.
Filters are great tools, but you can’t remove specific items from the results. When you need to do this, try Find All in Excel instead.How to use functions in Excel
There’s more to EOMONTH() in Microsoft Excel than the last day of the month. Learn how to put it to use in your spreadsheets.
In Excel spreadsheets, complex formulas are difficult to enter without making mistakes. Microsoft Excel’s LAMBDA() function can make such errors easier to find.
Using three Microsoft Excel functions, you can easily parse time components from a full date value.
If you need to conditionally split values into multiple columns in Microsoft Excel, consider using the IF() function. Here’s how.
A combo box’s autocomplete feature linked to a lookup function creates a flexible search tool.
Microsoft Excel’s dynamic array function XLOOKUP() might completely replace VLOOKUP() and HLOOKUP().
Populating a dropdown with a dynamic list is easy thanks to Microsoft Excel’s UNIQUE() dynamic array function.How to use numbers and calculations in Excel
There’s more than one method for multiplying in Microsoft Excel, and one of them doesn’t require an expression.
Entering fractions in Microsoft Excel isn’t exactly intuitive, but it is easy. Learn how to enter and display fractions as either fractions or as decimal values.
Making a floating bar chart in Microsoft Excel is a great way to visually represent distribution between entities. Susan Harkins will show you how.
At the sheet level, conditional running totals require focused expressions, but an Excel PivotTable requires only a few field swaps. Susan Harkins shows you how.
A straight ranking result is easy using one of Microsoft Excel’s ranking functions. Calculating a conditional rank is even easier if you let an Excel PivotTable do all the work.
If you need a monthly or yearly balance for revenue transactions in Microsoft Excel, you’re in luck because both are easy! Here’s how to find the totals you need.
Averaging values in an Excel sheet is easy. But perhaps you want to ignore zeros or include multiple sheets. Knowing how each averaging function works is the key to choosing the right one.
Averaging in Microsoft Excel is easy, until you start excluding specific values. Here are three ways to average a data set when giving special consideration to the highest and lowest values.
Everyone likes a bonus, but sometimes calculating one can be a bit complicated — at first. Here’s how to calculate the amount in Microsoft Excel that you or your staff will get paid.
Use formulaic conditional rules in Microsoft Excel to highlight the smallest and largest values within a period of years.
Microsoft Excel updates cell references when you copy an expression. Here are a couple of workarounds for those rare occasions when you don’t want to change the cell references.
Correcting Microsoft VBA code is easier if you understand what the basic error messages mean.
The easiest way to spot a unique value is to format it, and Microsoft Excel offers two ways to do so.
If you don’t want to display zeros, use one of these quick and easy methods to suppress them in Microsoft Excel.
With Microsoft Excel, you can create a worksheet that figures the hours worked for any shift. Follow these step-by-step instructions.
If a user inadvertently changes the formulas in your Excel workbooks, all your hard work could go out the window. This ebook explains how to protect and hide those formulas so they remain safe and intact.
Knowing how many days you’ve allotted to a project is important, but once you’re into the project, knowing how many days remain might be more important.
Simplify your data transformation by skipping complex parsing expressions and instead using Flash Fill and Power Query in Microsoft Excel.
Learn how to use Excel Power Query’s extract and split column features to extract delimited strings into their components with this step-by-step tutorial.How to use conditional formatting in Excel
The ramifications of missing a due date can range from simply adjusting the date to getting fired. Don’t take chances with deadlines when a simple conditional format can remind you.
Reporting lots of large values in Excel is probably a good thing, but if you want them to be readable, try this easy technique.
Learn how to add a highlight to expose groups in your Microsoft Excel data using a helper column and Excel’s conditional formatting feature.
Thanks to the linked cell property in Microsoft Excel, it’s super easy to use the selected value as a condition in a macro or conditional formatting rule.
This formulaic conditional formatting Excel rule will let viewers determine how many top values to view on the fly.
Formulaic conditional formatting rules in Microsoft Excel can be tricky, so learn how to avoid a common mistake.More Excel tips, tricks and downloads
Microsoft Excel users, follow these two easy methods to reduce data entry time and errors when customized features aren’t available.
Microsoft Excel has an import wizard, but if you structure the text correctly, you can bypass the wizard altogether.
The busier a Microsoft Excel workbook is, the more ways you need to get around in it. Check out these shortcuts to help you move between sheets.
Renaming an Excel sheet is a quick and easy task, but there’s more than one way to do it. Susan Harkins will show you how.
If you have to work with a date stamp in Microsoft Excel that includes date and time, you can use these simple expressions to extract both components, making them easier to work with.
Use these six shortcuts to select Table elements and insert columns and rows quickly in Microsoft Excel, making your use of Tables that much easier.
Filters are great tools, but you can’t remove specific items from the results. When you need to do this, try Find All in Excel instead.
Source data won’t always come in the form you need in Excel. When that happens, consider using Flash Fill to save time and aggravation.
Whether you’re tired of losing your spot when collaborating or you want to customize the way you view your data, sheet views in Microsoft Excel are for you.
If you run sorts in Excel a lot, you might benefit from shortcuts or even a macro. Here are some ways to make your sorting life easier.
If multiple users work in the same Microsoft Excel file, you can limit their access to only the ranges where they need to work.
Avoid the hassle of manually importing Word form data into Excel. With the help of an Excel wizard, you can quickly step through the process.
You’ll need more than one trick up your sleeve to find duplicates in Microsoft Excel.
Printing Microsoft Excel spreadsheets can be tricky, but you can avoid most printing problems by following these tips.
Sparklines are in-cell charts in Microsoft Excel that are easy to create and extremely helpful, but they can be more insightful with just a few specific settings.
Sparklines are a great visual tool, but you can increase their impact with a little simple formatting.
The tips, tricks and shortcuts in this ebook will help you get extra mileage from Excel’s powerful features, generate accurate results and save time on your worksheet tasks.
Avoid the hassle of manually importing Word form data into Excel. With the help of an Excel wizard, you can quickly step through the process.
Excel offers three what-if analysis tools that can sharpen your decision-making and help you find the best route to accomplish your objectives. This e-book introduces these tools — Goal Seek, Scenario Manager and Data Tables — and demonstrates how you can put them to work. Sample files are included in the download.
Often known as the universal data munging tool, Excel is trying out new options for processing messy text as well as entering it more quickly and accurately in the first place.
Microsoft Excel was first released in 1985, and the spreadsheet program has remained popular through the years. You can master Excel by reading these tips and tricks on how to add a dropdown list in an Excel cell to find duplicates, how to delete blank rows in Excel and more. SEE: Microsoft Excel: Become an…