Accountants

5 Excel Tips and Tricks for Accountants

Are you working with spreadsheets containing copious amounts of data? Having trouble making sense of it and need some shortcuts? Well, you have come to the right place!

With Financials for Office 365, you can export into excel sheets.  But from there what can you do?

# 5 - Setting formatting rules for specific numbers

Ever wanted to automatically make a cell's text green when the number inside is greater than 0?

Here's how:
  1. First highlight the cells where you want your rule to take effect.
  2. Under the Home tab, click on ‘Conditional Formatting’ a ‘New rule’.  Now you will see a lot of default rules available. I personally prefer the second one ‘Format only cells that contain’.  s-2.png
  3. In the 1st drop-down list, keep ‘Cell Value’.
    • Pick ‘greater than’ in the 2nd drop-down list.
    • Enter 0 in the third box.
    • Click ‘Format’.
​​​             ​s2.png
          4. Change the colour to green. When you’re happy with the formatting, click ‘OK’ and click 'OK' to create the new formatting rule.

              s3.png


#4 - Freeze Panes

Want to keep a column and row showing while your scroll to compare figures easily?

Here's how:
  1. Click on the one cell/columns/rows where you want your freezing to end. In this example, B2 is the cell in the example.
  2. Click 'Freeze Panes' under the View Tab.
             s12.png
Scrolling two rows down...
             s13.jpg
Scrolling one column to the right...
             s14.jpg

#3 - Copying Visible cells only

Have you hidden cells from view and then you want to copy them to another sheet, but when you highlight and copy it copies both hiden and visible cells?  Take these steps to only copy what you need.

Here's how:
  1. On the Home Tab, click 'Find and Select' and choose 'Go To Special'.s123.JPG 
  2.  Choose 'Visible cells only' and click 'OK'.  This highlights all of the visible cells.
  3. Then, simple 'Ctrl C' to copy or right click and copy and paste where you require the fields.

#2 - Summing up using conditional formulas

Have you ever had to manually select fields you wanted to sum up?  This can be quite difficult with over a hundred fields to filter over.  Do it smarter and use product sum formulas.

Here's how:

SUMPRODUCT is the formula.  For example...
s124.png

Formula in field B7 is...
=SUMPRODUCT((LEFT(A2:A5,1)="P")*(B2:B5))

There are 2 conditions in this example.  The first condition states that the first letter of each expense name must start with a “P”. The second condition states sum the respective prices of each expense. There needs to be an asterisk in between conditions in order for the formula to work.

#1 - Adding multiple rows at once

Do you need to add a dozen plus rows in a spreadsheet?

Here's how:
  1. Highlight the region you would like to add the multiple rows.                           Picture1.png
  2. Right click the highlighted region and choose 'Insert...'.  
  3. Choose 'Entire row'.                                                         Capture-1.JPG
  4. Click 'OK'. This will provide you with the same amount of rows as selected region.


Those are just 5 Tips and Tricks that will help to make your life easier when going from Financials for Office 365 into Microsoft's Excel.