SIMPLE EXCEL FUNCTIONS YOU SHOULD KNOW
December 9, 2021
Stacking in EXCEL
Recently I received data in a pdf file. The snapshot of the data looked like this:
Here we have tables containing the same columns i.e. Serial no., Pin code and Type. There were 182 pages of this data. The output should have all data in only 3 columns in an Excel sheet.
Here’s what I did:
1. Copy all the tables at once. Use CTRL+A shortcut.
2. Open an Excel workbook.
3. Paste the tables: CTRL+V.
The pasted values looked like this:
The first row of all the three tables are pasted in a single cell.
1. Now select the entire column > Go to DATA tab > Text to column > Delimited > Next.
2. Select Space in the Delimiters options.
3. In the data preview we see the data is now separated into different columns.
4. Click Next & Finish.
The table looked like this (Excel screenshot). The last line of the table contains 9356.
5. So we cut and paste the columns from D to I (marked in blue) below it and then again repeat it for the last three columns.
After cut and paste the table was like this:
Here row number should have been 28069 (extra one for the Column Headers at the first). On zooming out it was found out that there were some rows that had these headers (S.N., PINCODE, and TYPE). On filtering (ALT+D+F+F) S.N. There are 546 rows of data that should not be present in the table.
I selected the other two columns (CTRL+SHIFT+ARROWS: shortcut for selection) > ALT+ ‘;’ (selects only those individual rows containing unwanted data) > CTRL+ ‘-‘(deletes all those rows).
On clearing the filter we have only those data which is required and the last row is on row no. 28069.
Here I have used basic Excel tricks and shortcuts. Hope you liked reading it. If you know another way to solve this please do write it in the comment section.
If you have any experience of playing with Excel, please share it with us. We would love to know about it.
Thank you.
Contributor : Team Leveraged Growth
