Have you stared at the data in your worksheet and wondered how you were going to get the same information in multiple cells, or how to automatically insert a cell with a full name from two cells with the first and last name, or how to best graphically display those sales figures? Well, hopefully, these 5 amazing must have Excel tips will give you some answers!

Enter same content in multiple cells simultaneously

Need the same labels or values in several cells?

• Select the first cell
• CTRL click on the other desired cells
• Type Desired content
• Press CTRL ENTER

Presto! Same content in all the cells.

Same content in multiple worksheets

Need different content in several cells to display in multiple worksheets in the same cell references? Group worksheets and just type it once!

Group desired worksheets by selecting worksheet name tabs:

• If worksheets are not adjacent, click first one, and then CTRL click on each worksheet you want to have identical content.
• If adjacent, click on first one, and SHFT click on last one (or right click and choose Select All).
• Now type in as many cells as you want on the active worksheet (can be different data in each cell). Same contents are now in corresponding cells in all selected worksheets.
• To Ungroup, click on a worksheet tab outside of the group, or right click on a worksheet tab and choose Ungroup Sheets.

TIP: This is a terrific shortcut for adding the same headers and footers or printer or page setup settings to multiple worksheets simultaneously. Also works for creating duplicate content for days, months, quarters or any repetitive unchanging data; basically creating an in-workbook template!

Number Worksheet Rows

Can’t figure out number of rows because your data starts several rows from the top or some other area of the worksheet?

Excel allows you to number the rows and will auto adjust when you add or delete them if you use the =ROW() function. The key is to deduct the number of rows above the one you are starting in so the numbering will always be correct.

Example: We’ll use Row 12:

• Insert a column or click in the column where you want the numbering
• Type: =ROW()-11 in row 12 of that column. The number 1 is displayed
• Fill down desired number of rows.

You now have the actual number of rows you are working in!

Speaking of numbering, getting Excel to automatically create a series of sequential numbers, requires some gentle nudging…

Sequential Numbers

I you want to populate a column with sequential numbers, just enter the numbers in cells, such as

1

2

Select both cells and drag the Fill Handle for as many numbers as you wish. Excel will automatically iterate the count. Start with any number.

It will also work with jump numbering, such as this:

1

5

Select both numbers and drag down for the desired number. The next numbers would be 9 and 13.

Note: This also works with dates and text.

TIP: Here’s a trick for fast sequential numbering; just type your first number, hold down the CTRL key and drag the Fill Handle. Excel will recognize the pattern. (For non-sequential numbers, you still need two examples).

Like then so far? Let’s look at a couple more must have Excel tips!

Flash Fill

Flash Fill is a time-saving feature that reads patterns in adjacent columns and automatically fills the remaining cells in a column based on those patterns. It is useful when you need to concatenate (join) cells, or separate information in cells without wanting to write a cumbersome formula. Kind of like AutoFill on steroids! This amazing tool will make short work of many repetitive tasks, all without formulas (or the Text to Columns feature)! Here’s some things it can do:

Splitting Input Data

You received a huge spreadsheet where someone has put first and last name in one column, and you need them separated for sorting and filtering. (This could be any data, i.e., department name and phone extension, or salesperson and monthly sales figure. Doesn’t matter as long as the data has a separator, such as a space). Flash Fill to the rescue…

Example:

Full names are in Column A.

Steps:

• Insert: two columns to right and enter appropriate column header labels.
• Type: Fred, in appropriate cell (in example, Cell B2)
• Press: ENTER
• Type: Mary (note that Excel has figured out what you are doing and is displaying the remainder of first names, and is awaiting your approval)
• Press: ENTER, and presto, all the rest of the first names are added!

Repeat the above steps in the cells for Last name (In example, Cell C2)

List is now complete:

TIP: You can have flash fill complete only the last name, i.e., just insert one column, and instead of typing “Fred”, you would type “Frump”, ENTER, then “Lamb”, ENTER, and you would be done.

Note: Give Excel one or two examples of what you are trying to accomplish so flash fill can see a pattern. It will work with text, dates, numbers and time. Be aware that if you change source data, cells containing flash fill data will not update as there are no formulas involved.

Combining Data from Different Columns

The reverse is also true. You have a worksheet with first and last names (or any data) in two columns and you want them combined into one. This used to require the CONCATENATE function but now Flash fill can do it automatically. Drop kick that CONCATENATE function off your spreadsheet. Tough enough to spell it, let alone use it!

Here’s the data in two columns and how to combine into one:

Steps:

• Insert: a blank column to the right of split data
• Type first full name in the new column (C2)
• Press ENTER
• Type second full name, and before you are finished, Excel sees you want the remainder filled in
• Press ENTER to complete the list

Extend the Combined Data

You just got word that you need to add the email addresses for all the people in your company that you just did your flash fill magic on (or any such list). To make it worse, the addresses are last name first. Flash fill is not intimidated and has your back:

• Insert your new, blank column to the right of the Full Name column
• Type the email address in the first cell
• Press ENTER
• Start typing the second email address, and the dependable flash fill displays
• Press ENTER to complete the email list for the remaining cells.

You have saved so much time, you go to coffee!

Keyboard shortcut lovers…Here’s an even faster way to activate flash fill. Type the entry in the first cell. Press CTRL ENTER, and then press CTRL E and the column is filled in a flash!

TIP: If you accidentally lose the Flash Fill display, click the Data tab, and in the Data
Tools group, click the Flash Fill icon (ensure you are in the second entry in your column). If the ribbon and Flash Fill are greyed out, click outside your data range and then click in the second cell again and retype.

There is so much more that Flash Fill can do, so play with it and see if it will display that very thing you want.

Note: If Flash Fill is not working and the above tip doesn’t activate it, it may be corrupted or missing system files. There is an easy fix and download thanks to Microsoft Gold Certified Partner, Scott Chan. Just follow the information and link on his website: Scott Chan PC Support

The Quick Analysis Tool

You’ll find this a really helpful feature whether you are an experienced user or fairly new to Excel. Just select the data to analyze, and the Quick Analysis icon appears in the bottom-right corner of the selected data.

Click that icon, and a dialog appears showing a range of tools for analyzing the data, such as Formatting, Charts, Totals, Tables and Sparklines. Click any option, and a series of selectable choices appear; preview those choices by mousing over them. Next, click the option you want to apply it to your data. This feature speeds up the process of formatting, charting and writing formulas.

Steps:

• Select: the cells to analyze. When the mouse is released, you see the Quick Analysis Tool icon at bottom right of the selection:

• Point: at the icon to display the tool tip:

• Click: the icon to display the options:

• Roll your mouse over each of the options and when you see the one you want, just click!
  • Formatting: Adds Conditional Formatting to your selection based on Excel’s default rules
  • Charts: Displays the main types of charts that will work with your data
  • Totals: Sum, Average, Count…The equivalent of AutoSum feature. Running totals and percentages also available
  • Tables: Automatically converts your data to a table with the filters in every column
  • Sparklines: Displays miniature charts in the cells to the right of each row of data (3 types available)

Example of choosing Formatting: Selecting Greater Than under Formatting, Excel will automatically display the rule it used to color every cell with a number over 6065 pink. You can then make adjustments right on the screen. You can change the base number here and also if pink isn’t your color, change it here!

TIP: You can apply any one of these and then edit if you want to change the rule or behavior. For example, to see more options for the above, Greater Than Quick Analysis tool, (or any of the other Conditional Formatting options):

• Click the Home tab on the Ribbon, Styles Group, and click the drop down arrow on Conditional Formatting icon, (Note that many more conditional formatting choices are available on this drop down list).
• Select Manage Rules (bottom of list).
• Select the rule, and click Edit Rule to display options. (Can also Delete Rule here)
• Click OK to confirm any changes.

Note: If you want this tool to put in the formulas for you, such as totals, averages, counts, etc., don’t enter them yourself until you see if the automatic calculations under Totals: work for you!

See more awesome Excel tips and tricks here: https://gaylelarson.com/copy-and-paste-filtered-subtotals-or-visible-cells-only-in-excel/ or use Search for a specific topic.

Which feature is most helpful to you?