Our Blog

Get back to basics with Excel

by Megan Iemma on August 24, 2016 , 1 comment

Get back to basics with Microsoft Excel

Is Excel one of your favourite software products or is it something that you struggle using in your business? I suspect most of us have either a love or hate relationship with Microsoft Excel. I learnt how to use Excel in my ‘keyboarding’ classes. In the Microsoft Office Suite, Excel certainly wasn’t, and still isn’t my sweet spot!

Since having my business just over three years ago, I’ve had to re-acquaint myself with Excel. Fortunately for me, my business manager (a.k.a. my husband) has supported me in re-learning how to use Excel. So, in this blog post, we are going to get right back-to-basics with Excel.

Tip: A wise piece of advice for all small business owners…learn to use Excel (and early on in your business). If you are struggling to learn how to use Excel, and you would like to learn more, there are some great video  tutorials via Microsoft Office Support and Lynda.com

Lynda.com training videos

Budgeting

When you are doing your budgeting using Excel, do you know what the mathematical operands (signs) are? Why do you need to know these, you might ask? The answer is you need these symbols so Excel can add or subtract or multiply or divide your data. So:

  1. Shift + (plus sign) = plus
  2. – = Minus
  3. Shift + 8 = Multiply
  4. / = Divide
  5. Equals =

Tip: When you are typing a formula, you must start with  ‘=’, and remember NO SPACES with any formula.

Adding Numbers up in a column or row in Excel

When I’m working on my financial records in Excel, it’s important for me to add my #data. There are two options to complete this task.

Solution A: Highlight the numbers either in a vertical column or a horizontal row, then go to the HOME ribbon and click on the AUTOSUM button.

Microsoft Excel Formulas screen

Solution B: Click on the ‘cell’ (box) where you would like the answer to appear, then type =sum( then highlight the cells you would like to calculate (the data will appear in the box) and then close bracket. To complete this formula, press ENTER and your answer will appear.

Excel sum basics

 

Formatting Cells within an Excel Spreadsheet (single page)

To highlight an ENTIRE ROW or ENTIRE COLUMN, click on the LETTER for a COLUMN or the NUMBER for the ROW. Then right click FORMAT CELLS > CURRENCY and choose the decimal places. If you would like to show ‘cents’, change it to ‘2’ decimal places, otherwise leave it at ‘0’ for whole dollar amounts.

Tip: If you would like to manually just change one/multiple cell, use the shortcut CTRL + SHIFT + 4 to change it do a $ amount with two decimal places.

currency format cells excel

Format Date: Highlight an ENTIRE ROW or COLUMN: Then right click FORMAT CELLS > DATE  and then select chosen format (Type). This is then defined for that ROW or COLUMN.

Tip: Why do you need to know this? Get Excel to compute the due dates of your invoices. Us the formula =(cell with the issue date)+14 (example below) to know when your invoices are due 14 days from the date of issue. If your terms are 7 days, then replace 14 with 7. If your terms are 28 days, then replace 14 with 28.

invoice due dates excel

Freezing Rows and Columns (Microsoft Office Support has a really good tutorial on how to use this).

Tip: Freezing rows and columns allows you to scroll up and down or from left to right without losing sight of your headings.

7 Excel Shortcuts you can’t live without

  1. Shift + Tab: This helps you move across one cell at a time (left or right).
  2. Ctrl + T: To create tables
  3. Ctrl + or Ctrl – = To add/delete a row or column (rather than manually right click)
  4. Double click between the column headings to auto-resize to the widest string of text/numbers. Same for double clicking between row headings (from Yvonne Adele  – another Microsoft #SMB Ambassador).
  5. Wrap Text: Home > Wrap Text (this gets rid of messy cells where the text doesn’t format to the cell exactly).
  6. Merge Cells for headings: Home > Merge (This drop down menu has four options – Merge and Centre, Merge Across, Merge Cells, Un-Merge).
  7. Insert a new WORKSHEET into a Workbook (worksheets are just #Excel pages in the same document). ALT + H + IS

A big thankyou to the team at Microsoft for sharing their shortcuts, my group #TechMadeSimple and also the other #MicrosoftSMB Ambassadors who have shared their #Excel wisdom.

For all the shortcuts, I couldn’t add in here, check out this list for #MicrosoftExcel #Shortcuts 2016.

This article has been probably one of the most challenging blog posts I have ever written. I hadn’t realised that my #financialliteracy isn’t as good as it should be, or even my understanding how to use #Excel for my business. This is a good lesson for #smallbusiness owners…understand how to manage your financial records. So the next time you need to use Excel, brush up on your skills. Don’t forget to watch some of the videos from Megan Iemma TV on YouTube.

 

Megan IemmaGet back to basics with Excel

Related Posts

Take a also a look at these posts

1 comment

Join the conversation
  • Megha - August 26, 2016 reply

    Remove Duplicate values in set of data
    Select the data set
    Under Data Ribbon, select Remove Duplicates
    Highlight Duplicate Values
    Select Data set
    Under Home ribbon select Conditional Formatting -> Highlight Cells Rules -> Duplicate Values
    Another very useful one is Select Visible Cells, selects only the cells that are visible when filters are on or rows and columns are hidden

Join the conversation