Useful Excel features to work with a lot of data

Excel 2016
If you have a lot of data, manual editing and formatting of cells isn't an option anymore. However, Excel provides many group operations that make those manipulations quick and easy.

   1.   For better navigation in the workbook with many sheets or to highlight some important data, it is possible to create hyperlinks to another cell in the same spreadsheet.

Hyperlinks in Excel 365

See The best way to organize your workbook using hyperlinks for more details.

   2.   If you need to copy the range with blank cells and paste only non-blank cells, Paste special with Skip blanks doesn't work as expected and inserts blank and non-blank cells, and you need to remove blank cells manually. However, a combination of filtering and copy/paste allows you to handle large ranges of cells effectively.

Filter in Excel 365

See Copy and paste only nonblank cells for more details.

   3.   To select all cells in the sheet, you need just one keystroke or mouse click.

Select all cells in Excel 365

See Quick way to select all cells for more details.

   4.   Filter, Sort, and some other functions don't work well with merged cells, and large unmerging ranges of cells can be tedious, but there is a trick to make it easier.

Paste values to unmerged cells in Excel 365

See Workaround for sorting and filtering of merged cells for more details.

   5.   Viewing the same worksheet in multiple windows arranged horizontally or vertically allows you to review and clean data quickly.

New window in Excel 365

See Viewing a worksheet in multiple windows for more details.

   6.   You can group and ungroup sheets for fast copy/paste and format changes.

Grouped sheets in Excel 365

See Grouping and ungrouping sheets for more details.

See also this tip in French: Fonctions Excel utiles pour travailler avec de grandes quantités de données.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.