Excel tips for the OC like me

I am a ‘little’ OC when it comes to my documents. Are you showing me a procedure manual using a Word document? Then I would have to expect some headings there and a Navigation Pane clearly outlining them. Are you showing me an Excel spreadsheet with a huge amount of data? Might as well please do a Ctrl+Home before saving so I’m in the first part of everything when I open the file. If you’re with me, here are some Excel tips you can apply to keep things orderly. But imagine this first…

So a process was migrated to your team and a lot of files and shared drive accesses are being transferred. You try to go through the process flow documentation, religiously follow “step one, open the Master File.”

*Double-click.*

“Circular Reference Warning”

*Clicks OK*

“This workbook contains links to other data sources.”

*Clicks OK with your eyebrows trying to raise and meet with other*

And the Master File presents you with –

It is the Master File.

  1. Apparently the last update was made in cell G5.
  2. Account numbers leading the columns – sure. You see some accounts are a group with the same treatment when it comes your process, and so they are literally grouped in one cell.
  3. Ooh, some accounts are critical ones with stricter deadlines, you can see it ‘cos the cells are in yellow, and some in brown.
  4. Some specifications are indicated in the column headers and you see “Yes” or “No” – or “YES” or “NO” – for each account if it applies.
  5. You try to filter through the dates under the Client Deadline column, but presents you with some inconsistent “dates.” So you tick the third of January and then scroll through the remaining to tick the ones starting with “03/.”

You keep your calm and stick with learning the process and familiarizing the Master File. Attempting to organize it, you get a complete and updated list of accounts from another file and copy-paste it to a fresh, new workbook.

*Types =VLOOKUP etc. to the next cell and double-clicks the bottom right cursor*

  1. A number of “N/A” results show. You check it to the Master File and sees it there. Hmm.

These scenarios flicked the heck out of me when I was still in my 7-4 job – a lot. It was a new process so I didn’t revamp the whole thing right away so as to keep the entire database intact. So the inner Monica Geller in me had to stay put real hard and wait for a reasonable time until the team’s learning curve has stabilized.

Creating or revamping a simple Excel database takes a lot of detail tweaking especially if you’re an OC or a control-freak like me. I’m making this post 80% to share with you quick wins on how to keep Excel information organized and flexible; and 20% to vent that itchy feeling from being an OCD.

TRIM

Your #6 above is probably caused by spaces after the text. It’s after, so it’s a blind spot. You would want to use the TRIM function to delete all unnecessary spaces in your text. Unnecessary ‘cos Excel is smart enough to know it – before the text, double spaces in between words, or after the text. Syntax is pretty easy:

=TRIM(text)

Your text is now free from unwanted, unseen spaces! Going back to the VLOOKUP, if you still see N/A, the next few functions could help you.

LEN

If your account numbers are supposed to have a fixed number of characters, say, 6, then some of those in the Master File could have missing or extra characters in it. You would want to use the LEN function to count the number of characters there are in a text, including the spaces. Syntax is also easy:

=LEN(text)

Stray or missing characters are now easy to spot for editing! If you still see N/A on our VLOOKUP earlier, it could probably be a legit error so go manually cross-check those items! To avoid this inconsistencies in our account number, which acts up as the database’s unique identifier, you may wanna use…

DATA VALIDATION

Control your input firsthand by using a restrictor of what must a certain cell contain. In our organizing scenario, the following could be of great help:

List

We have a list of accounts already, why not keep them in a dropdown cell and limit the user to only input those in that list?

  1. Place cursor in the cell where you like your dropdown to appear.
  2. Go to
    1. DATA
    2. Data Tools group
    3. Data Validation
    4. Settings tab
  3. Click List from the dropdown menu
  4. Type or click through the range of data you want to dropdown to show.
  5. Press Enter, click OK.
  6. Fill down or copy-paste the first cell we put Data Validation in through the entire column.

Text Length

Or we could make our input cells on free text mode, partially free, by specifying how many characters should a cell contain. In our case our account numbers should only have six characters.

  1. Place cursor in the cell where you would want your dropdown to appear.
  2. Go to
    1. DATA
    2. Data Tools group
    3. Data Validation
    4. Settings tab
  3. Click Text Length from the dropdown menu
  4. There are a lot of limiters to choose from but in our case we have a specific value in mind, which is six, so we select equal to.
  5. Type 6, press Enter, click OK.
  6. Fill down or copy-paste the first cell we put Data Validation in through the entire column (actually you may do this first thing, like in the below GIF).

CTRL+HOME

Get your cursor home! Get your active sheet on the first tab! Unless you want the reader to see the main tab of your workbook, which questionably is not the first sheet, the first thing a reader must see when he opens the file is the first sheet, with the cursor on the top left of the unfrozen cells. It looks professional. It gives the impression that the file is really user-friendly.

So those so far are the Excel functions that satisfies the OC in us when it comes to unique identifiers in our database. These are unique identifiers – they are the glue to hold all other information in one place; the one keyword we search for if we want something in our reports or decision-making or tracking; the first point of input in our database, so sacred we would want to start everything right in our file. So it’s important that we organize this thing first so all else follow a coherent flow of input afterwards.

Hope the small list helps beginners out there, OC or not, too.

You may want to be in the loop for the next part of Excel bits for the OC, so go sign up for newsletter or follow me in any of the below accounts!

 

>> Lae

Lae

Hi! I am Lae from the Philippines. A CPA and frustrated blogger who writes mainly to create a portfolio for her accounting freelancing. I'm an Excel enthusiast in that VBA, complex formula, and Pivots fascinate me. I'm also down for cool PowerPoint presentations and visual digital documents. On the side I'd be putting in some travel experiences here in LDR as my repository of cities, culture, and people.

Leave a Reply

Your email address will not be published. Required fields are marked *