Microsoft Excel is an extremely powerful tool for making many different types of spreadsheets with all kinds of different data. You can format your sheet with fancy text, graphs, charts, pivot tables, and more.
But there are also simple, easy ways to improve your Excel experience without having to master these really advanced Excel concepts. When you’re learning Excel, it’s helpful to get familiar with tips, tricks, and shortcuts that will only make it easier for you to best utilize the advanced parts of Excel later on!
Here are some of the most useful, simple Excel hacks for the easiest navigation and best practices for managing data and making more effective and organized spreadsheets early on in your Excel journey.
Excel Basics to Know First
Before you dive into Excel tips and tricks, you first must understand the bare bones basics of Excel. Without knowing the basics, you won't be able to navigate the spreadsheet software as easily as you'd like and you may find yourself getting frustrated.
With every major update, more and more possibilities are opened up. Currently, Excel has hundreds of applications. The most popular include:
- Accounting
- Budgeting
- Data Entry
- Data Analysis
- Management
- Administration
- Sales
- Data Organization
- Data Reporting and Visualization
- Inventory
Take a few moments to explore a sheet in a fresh workspace and learn where the essential features of Excel are located so you can start using them with ease!
Features of Microsoft Excel
Formula bar: This is the space where you can type your formulas and text
Formula: The mathematical equation you write in the formula bar
Function: A predefined text prompt you can type in the formula bar to indicate for Excel to perform a specific action.
For example, =SUM means to add up a range of data
Columns: A column is the vertical alignment of cells in a spreadsheet, indicated with letters
Rows: A row is the horizontal alignment of cells in a spreadsheet, indicated with numbers
Cells: Each rectangle in the spreadsheet is a cell, indicated with the coordinates from the column and row.
For example, C5 is the cell located in column C and row 5.
Data Validation: Ensures entered data values fit into certain parameters.
Conditional Formatting: Change the physical appearance of cells based on certain criteria.
Relative Reference: When copying and pasting a formula, Excel will automatically change the referenced cells to correspond with their new placement. For example, if your formula references the cell right next to it, when you copy and paste that formula 10 rows down, it will reference the cell in the column right next to it 10 rows down as well.
Absolute Reference: Ensures Excel will not use relative reference when copying and pasting cells. Use “$” to indicate an absolute reference.
For example, =B10+$A$1
Freeze Panes: Keep certain rows and/or columns frozen in place so you can see them even while scrolling.
Merge Cells: For formatting purposes, you can merge cells. Typically, this is used for creating section headers and other things that need to stand out.
Add/Delete Rows/Columns/Cells: You must know how to add cells, rows, and columns in places rather than move all the data over. You must also know how to remove extra rows, cells, and columns.
Dropdown Menu: Adding a dropdown menu helps streamline certain types of data entry.
Checkbox: Adding a checkbox to your spreadsheet can be helpful for things like conditional formatting.
Charts: Knowing how to create data sets that can be translated into readable, reactive charts is imperative.
Table: A table is a structured range of data organized with rows and columns. You can make an entire sheet into one table, or you can have multiple tables inside one Excel document; it depends on what you need your data to show.
Excel has over 450 built-in functions that you can use and combine in your spreadsheets.
Sort: You can sort your data by any parameter available (based on the column or row titles you assign). This makes viewing data values in different ways much easier.
Filter: Similar to sorting, filtering means you change the way your data is presented in the spreadsheet. You can always remove filters to make the data go back to its original form. Filters can be added at the top of a column.
Sheets: You can create multiple sheets in each Excel document. This is indicated by the tabs at the bottom of the document.
AutoFill: You can automatically fill in repetitive data and information with the AutoFill feature, sometimes called “Drag & Copy.” Click on the lower right corner of the cell you want to use for reference and simply click and drag your mouse over the row or column you want to AutoFill. This is one of the most helpful features of Excel.
Examples: Instead of writing 1/1/2024, 1/2/2024, 1/3/2024, etc, you could simply write the first date, then click and drag down the column for as many days as you need listed. Then Excel will automatically fill in the dates. You can also copy formulas, functions, and more with this feature.
FlashFill: Excel will identify patterns in the way you are entering data and automatically suggest input for the remaining cells.
Format Painter: If you create a custom format for a certain cell and want to make another cell with that same format, you can simply use the paintbrush to copy the format to the other cell.
Wrap Text: Clean up your data visually by selecting how you want large blocks of text to appear. You can choose to have the text spill onto the adjoining cells in the next column or wrap it, turning it into a block of text and adjusting the cell height to accommodate the text automatically.
Comments and Notes: Add comments and notes to specific cells in a document for your own reference or when working with a team. Comments can be commented on by other users for discussion, while notes are simply notices.
Autofit Column Width & Row Height: Automatically make the row or column expand or contract to show the longest string of data without cutting anything off or leaving extra space at the end.
Hide and Unhide: You can hide columns, rows, and even whole sheets that you want to keep around but don’t want to see all the time and unhide them at any time
Best Excel Tips for Fluency
Learn these practices early on in your Excel usage to more effectively use Excel later on.
Having poor habits in any software can lead to a tangled mess of unforeseen issues later on. For example, if you don't have a good way of naming your files and then you start saving dozens of files per week, you'll quickly lose track of your older documents. Therefore, coming up with an effective naming convention before you have too many documents is essential.
Here are some of the most important things to keep in mind when you start using Excel.
Best Practices for Using Excel
To skillfully navigate Excel, there are some habits you should get into even while you’re still learning. It’s much easier to develop these good habits now rather than try to unlearn the bad habits later.
Use your keyboard: Almost everything you can do in Excel you can do without a mouse. While memorizing every keyboard shortcut at the outset is unrealistic, get used to learning new shortcuts and using the arrow keys to navigate around the page as much as possible.
The bare minimum you should be able to do with your keyboard includes:
| Action | Mac Shortcut | Windows Shortcut |
|---|---|---|
| Save | Cmd | Ctrl+S |
| Cut | Cmd+X | Ctrl+X |
| Copy | Cmd+C | Ctrl+C |
| Paste | Cmd+V | Ctrl+V |
| Undo | Cmd+Z | Ctrl+Z |
| Redo | Cmd+Y | Ctrl+Y |
| Select cells, rows, and columns | Shift+Arrow keys | Shift+Arrow keys |
| Delete all contents of multiple cells at once | Fn+Delete | Alt+H+E+A |
| Start a new line in the same cell | Option+Return | Alt+Enter |
| Make a new sheet | Cmd+N | Ctrl+N |
| Switch to a different sheet | Cmd+ - | Ctrl+Tab |
| Insert a function | Type "=" and the name of the function you want | Type "=" and the name of the function you want |
| Go to the cell to the right | Tab | Tab |
| Go to the cell to the left (Tab backward) | Shift+Tab | Shift+Tab |
| Go to the cell below | Return | Enter |
| Go to the cell above | Cmd+Shift+Return | Shift+Enter |
| When not in "cell activation" mode - navigate the spreadsheet | Arrow keys, Fn up, Fn down | Arrow keys, page up, page down |
Excel was the first ever computer application to utilize a toolbar!
Other things to make a habit of when learning Excel are:
Avoid Circular References: Circular references are when formulas and functions reference themselves when making a computation.
Make a Master Copy: Always make a copy of a template and modify that instead of modifying the original.
Prevent Other Users from Making Changes: Protect your sheets to avoid accidentally making changes and prevent others from making changes. This will also help ensure that you and others always make a copy instead of saving over the master copy!
Proper Naming Conventions: Create an effective way to name your worksheets well and keep track of new versions, always use an underscore “_” instead of a space in file names because spaces can cause errors in certain applications.
Clean Up Your Active Workspace: Check for the last populated cell to ensure there isn’t any stray data and condense the used range.
Making Dates Accessible to International Users: Use dd-mmmm-yyyy if you will be sharing your work with international people, since using mm/dd/yy is usually indistinguishable from dd/mm/yy. For example 01-Mar-2024 or 01-March-2024 instead of 3/1/24.
Writing Functions, Formulas, and More as Text: Use a single quote/apostrophe (‘) before a number that starts with 0 or before an = if you want what you’re writing to be treated as text instead of a formula.
For example, if you want a cell to display “=SUM(A3:A15)” but you don’t want Excel to actually make the calculation, simply type the formula with a ‘ at the beginning, so it looks like '=SUM(A3:A15).
Excel Keyboard Shortcuts
Beyond basic keyboard shortcuts, there are more advanced tricks to know and make your data entry in Excel much easier. Check out this table of some of the best Excel shortcuts for learning Excel spreadsheets tips.
| Shortcut | Action |
|---|---|
| Alt+H+W | Wrap text of selected cells |
| Alt+H+O+I | AutoFit selected columns |
| Alt+H+O+A | AutoFit selected rows |
| Ctrl+Shift+(arrow key) | Select all cells in the direction of the arrow key(s) you press |
| Ctrl+Shift+End | Find the last bottom right cell with data |
| Ctrl+; | Inserts today's date |
| Ctrl+Shift+# | Changes the format of the date |
| Ctrl+B, Ctrl+I | Apply bold or italic to selected cells or highlighted text |
| Ctrl+5 | Applies strikthrough to selected cells or highlighted text |
| Ctrl+0 | Hides the current column |
| Ctrl+9 | Hides the current row |
| F2 | Edit the current cell (like double-clicking) |
| Shift+F10 | Opens the right-click menu for the selected cell |
| Alt+H+A+C | Center-align cell contents |
| Alt+H+H | Add color fill to cell(s) |
| Alt+H+B | Add border to cell(s) |
| Ctrl+Shift+_ | Remove border |
| Shift+F2 | Add comment to a cell |
| Shift+F10+D | Delete a comment in a cell |
| Ctrl+H | Open the "find and replace" window |
| Ctrl+A | Select all |
| Shift+scroll with mouse | Scroll left and right instead of up and down |
Most Useful Excel Functions
Excel comes equipped with many useful pre-made functions that can help you sort and present your data more easily and effectively. Here are some of the best Excel functions you should know. Remember, you must put an = at the beginning of the function for it to work.
| Function | What It Does | Example |
|---|---|---|
| SUM | Adds the value of the selected cells | SUM(A1:A100) |
| AVERAGE | Finds the average (mean) of the values of a range of cells | AVERAGE(B2:B35) |
| IF | Receive the answer to a logical yes or no question about the data. Syntax: IF([logical_test],[value_if_true], [value_if_false]) | IF(F5>G2,“Out of Budget”,“In Budget”) |
| VLOOKUP | Locate specific data in a large spreadsheet. Syntax: (lookup value, range containing the lookup value, the column number in the range containing the return value, Approximate match (TRUE) or Exact match (FALSE)) | LOOKUP(B5,B1:C10,2,FALSE) |
| COUNTIF | Count the instances of data that matches custom search terms within a data range. Syntax: COUNTIF([where_to_search],[what_to_search_for]) | COUNTIF(A1:A10,"New York") |
| MIN | Find the smallest number in a data range | MIN(A1:A10) |
| MAX | Find the largest number in a data range | MAX(B1:B10) |
| SUMIF | Adds the value of the selected cells that match the constraint. Syntax: SUMIF([data_range],[criteria],[sum_range]) | SUMIF(A1:A10,"white paint",D1:D10) |
| CONCAT | Combines text from separate cells into one cell. | CONCAT(A1,B1,C1) |
Learn Additional Excel Shortcuts with Superprof
Want to learn more Excel tips to help increase your productivity? Find out all the different ways for creating formulas in Excel, Excel commands, best practices, data management, and more when you sign up for classes with an experienced Excel tutor!
With a private tutor to help you on Superprof, you can be sure to learn all the complex Excel formulas and Excel tricks and tips you need to be able to harness the true capabilities of this powerful datasheet program.
Excel is a highly effective data-organizing software that can be used for countless applications. The only limit is your ability to understand and use the various features! Start by getting familiar with the basics and working your way up to more complex Excel actions to make the most of your spreadsheet software.
Enjoyed this article? Leave a rating!
Loading...
