Useful Excel features to work with a lot of data

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

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

Hyperlinks in Excel 2016

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 2016

See Copy and paste only nonblank cells for more details.

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

Select all cells in Excel 2016

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.

Formula bar in Excel 2013

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 2016

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 2016

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.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more