Almost every enterprise in the country relies on Excel spreadsheets, but almost nobody takes full advantage of them. Here are a few tricks you may not be using.
Keep cell contents as precise and unique as possible
It is very easy to merge cells. Moving or pasting new data into them later can be a headache. It makes life easier if you think twice before altering the cell structure of a spreadsheet, and look for ways to sort unique data into each cell at the outset.
Names and addresses are a common problem. It is better to have forenames and surnames in different cells, and the same for street addresses and post codes. Customers hate filling details into lots of different boxes and make lots of mistakes, so data won’t always be delivered to you in a convenient format. The quickest way to solve this problem is to highlight a temporary column with the mixed data in it, then use “Text to Columns” from the Data tab.
You can also use this trick to extract a representative’s name from a company name by splitting up the different elements in an email address. Use the @ symbol as the delimiter instead of the space character.
Importing data from PDF documents
Until recently, Adobe made PDF files difficult to import. Although this can now be done, many users still laboriously copy information from them manually. The labour wasted on this operation is probably appalling as many wholesalers provide catalogues as PDFs while retailers need data from them in Excel.
You can rapidly extract data from PDF to Excel using Cloud applications. In other words, you only need to visit a website to complete the operation. As well as PDF to Excel you can find a host of other useful format conversions that can greatly accelerate your productivity, saving time and money.
Minimise VLOOKUPs in large workbooks
Microsoft tells us one spreadsheet can have a million rows and over 16000 columns but if it contains a lot of data it can start wilting much sooner. Sluggishness often begins after you have a few VLOOKUP formulas or VB scripts.
An alternative to VLOOKUP is INDEX MATCH. For example INDEX(table array, MATCH (lookup_value, lookup_array)). It is often useful in larger workbooks when importing between different columns in a different order on each page.