Excel Tips: Find and Delete Rows, Go to Special

Yes – another Excel post!  Today I was formatting income statements in Simply Accounting (the departmental income vs. budget ), (in Excel) – and in the process of doing so - I learned some neat Excel tools.

Although I used these to format an income statement, you can use these in a variety of applications!  Enjoy!

Tool # 1 – Find & Select, Delete Rows

Problem:  Rows with unnecessary data mixed together with the rest of your data. 

B          Office Supplies

C         Not Assigned to Departments          $xxx

D         Department # 1                                   $xxx

E          Other Departments                            $xxx        

Solution:

1.       Press “Ctrl” + “F”

2.      Type the word(s) you are looking for and click “Find All”(in the example above, I typed "Not Assigned to Departments")

3.      Select all of the search results by pressing “Ctrl” + “A”

4.      From the Home Tab click “Delete”then “Delete Sheet Rows”

Delete is on the Home Tab

Tool # 2 – Find & Select Blank Rows, Delete Rows

Problem:  Unnecessary blank rows in between your data

B          Office Supplies

D         Department # 1                                  $xxx

E                                                                     

F          Telephone Expense                            $xxx

 Solution:

1.      Select the column in question

2.      From the Home Tab, go to “Find & Select”

3.      Select “Go to Special”

4.      Select “Blanks”

5.      Click OK

6.      From the Home Tab, click on “Delete”, then “Delete Sheet Rows” (or simply press “delete” on your keyboard if you want to keep the row but clear the cell).

Find & Select, On the Home Tab - Look for the Binoculars

Tool # 3 – Find & Select  and Hide Rows

Problem: Rows that contain data, formulas, etc. that must be kept in the spreadsheet because they are part of other equations - but you want them hidden

1.      Select the column in question

2.      From the Home Tab, go to “Find & Select”

3.      Select “Go to Special”

4.      Select “Formulas” (As you can see, you have a number of options to select here)

5.      Click OK

6.     Press "Ctrl" + "9" (Hide)

And there you have it!  I'm always interested in learning new Excel tips & tricks - if you have any you would like to share!

Excel Tip: How to Merge and De-Merge Information

Written by:  Jeanne Franche

As a bookkeeper, I use Excel on a daily basis.  It is an enormous time-saver, and a very useful tool.  Therefore, I get very excited when I learn new tricks to get more use out of the program - and here I will share two recent tricks that I have learned with you.

The Concatenate Function

Let's say you run a little restaurant, and you have an Excel sheet to track which drinks and foods are the most popular in your store.  You have flavour in Column A, and Type in Column B.

You want to Subtotal this information, but the Subtotal tool in Excel will only total one column or the other (Flavours or Types).  You want to know how many units of "Tomato Juice" you sell, and also how many "Tomato Sandwiches" you sell.  The solution is to "concatenate" the two columns together, and then you can use the Sort and Subtotal tools (Covered in a future blog post).

How?

Decide which column you want the new Concatenated data to be in.  I chose Column C.  Click "Insert a Function", search for "Concatenate".

In "Text 1" choose the first data cell you want to Concatenate.  In "Text 2", you can enter a separator (recommended) such as a dash (-), though it is not necessary.  In "Text 3", chose the second data cell you want to Concatenate.  Example below:

Click "OK" and then your cell C will have the new, concatenated data (Orange-Juice).

Application

I typically use this function paired with sort and subtotal (which I will cover in future blog posts).  You can use this for virtually anything (such as, in the example above - tracking sales) to more specific uses.

A tip to anyone using Microsoft Small Business Financials:  This is a handy tool to use for exported Smart List account reports, to Concatenate accounts and projects that are displayed in separate columns.

How to "Un-Concatenate" 

Let's say you export some information from your accounting program (or CRM software) and the information is all together in one cell.   One example I can think of is client names and addresses.

Row A

John Doe, 123 Somewhere Street, Wherever, Ontario, P04 3Z5

This is frustrating, because you want to sort all of your clients based on city, or province, or by last name - but everything is together in the same cell!  How do you separate it?

First - you must select your column or cell, go to "Data", and then "Text to Columns".

Step 1:  You will have the option between fixed width or delimited.  I chose "delimited" because my data is separated by commas.  Fixed width will give you greater control over where to separate your data (i.e. You can have "Somewhere" and "Street" separated), but for simplicity - I've chosen delimited.

Step 2:  Select "Other" and enter the character that separates your text - in our case it's a comma.  You will see a preview of what your selection will do.

Step 3:  This screen will allow you to choose the format of each column.  In this example, I clicked Finish - but this can be useful if you have numerical values that you want to sum, or dates that you want to sort.

And Voila!  Now you're free to filter and sort based on whatever you wish, or simply reorder the columns (put last name in column A, first name in column B for example).  Now your data is much easier to manipulate!

And that's it!

Thank you for reading my blog - I would love to hear your feedback in the comments section below!

Self-Employed DIYers - Why Should You Hire a Bookkeeper?

Written By:  Jeanne Franche


You're self-employed and successful, congratulations!  You wear multiple-hats:  manager, salesman, marketer, tradesman.  But who handles your books?

If your answer is either "myself" or "my spouse" you may save hard-earned dollars, effort and time if you hire a bookkeeper.

Are You a DIY (Do It Yourself) Bookkeeper?

Many self-employed professionals try to save money by doing the finances themselves, or getting a spouse to do them.  But you need to ask yourself, how much is your time worth?  How much is your spouse's time worth?  If you or your spouse's occupation is something other than bookkeeping - it's likely that your time would be better spent on other tasks, such as drumming up more business.

Cost of a Bookkeeper

The going rate for a professional bookkeeper may seem steep to some, but it's important to weigh out the benefits.  For small companies, hiring a professional bookkeeper may be less expensive than having a full-time staff doing the books, if you factor in the costs of wages, benefits, vacation pay and downtime when business is slow.  Many bookkeepers will charge by the hour, so if there is only a few hours of work a month - that is all that you pay for!  Also - a professional bookkeeper will have experience (which is the advantage of working for multiple clients), and will therefore be able to do the job much faster than a non-bookkeeper would.

An added plus?  It will also save you money and headache when, at year-end your books are done and ready for the accountant at tax time (as opposed to a shoebox full of receipts).

How Is Your Business Doing?

A professional bookkeeper can tell you - with monthly financial statements, how your company is doing - instead of waiting until year-end to find out if you turned a profit.  Knowing how your company is doing month-to-month will help you make financial decisions that will directly impact your company, you will see your cash flow, control your expenses and cash outflow, and have more control over overspending.  You can even have your income statement compare against a  yearly budget or with previous year comparisons.

Accounting Programs

There is a vast range of accounting program products out on the market, ranging from user-friendly to extremely sophisticated.  Find a professional bookkeeper who will know how to navigate the program of your choosing - and if you are tech savvy, could show you how to pull reports yourself!  Imagine having years of your financial data at your fingertips, readily accessible within minutes.

How to Find a Bookkeeper?

Now that you're convinced that you need a bookkeeper, how do you find one?  Word of mouth is a good place to start - check with your accountants, fellow business associates, etc. - if they've had experience with a really good bookkeeper.

Do not hire a bookkeeper based on price alone - your risk is that you'll get what you pay for.  Do your research: check references, experience and, after year-end, check with your accountants.

Do you have any other reasons why DIYers should hire a bookkeeper, please post in the comments section below!