+64 9 520 9200      enquiries@alliott.co.nz

The Business Advisory Blog

Welcome to our blog

Insight, news and updates from Alliott NZ Chartered Accountants, Auckland New Zealand. The views expressed here are the views of the author and should be discussed in further detail should an article be relevant to your individual circumstances.

While every effort has been made to provide valuable, useful information in this publication, this firm and any related suppliers or associated companies accept no responsibility or any form of liability from reliance upon or use of its contents.  Any suggestions should be considered carefully within your own particular circumstances, as they are intended as general information only.

Email me when new posts are made to this blog

Moira's tips for Excel users

Written by Moira Kemp on July 25th, 2018.      0 comments

budget financials laptop-902Shortcut keys

Ctrl + Pg Dn or Pg up

moves to next worksheet

Ctrl + Shift + Pg Dn or Pg up

Groups all worksheets

Ctrl + Home

moves to top of spreadsheet

Ctrl + End

Moves to bottom of spreadsheet

Alt + Tab

moves between windows

Alt + E + S

To open paste special menu

F2

to edit the cell

F4

repeats last action and also adds $ sign to make an absolute cell

Ctrl + Shift + :

Insert Current Time

Ctrl + ;  

Insert Current Date

Ctrl + Shift + "

Copy Value from Cell Above

Ctrl + ’

Copy Formula from Cell Above

Ctrl + `

to view details of all cells

Alt + = 

AutoSum

Ctrl + +

Insert, (Rows, Columns, or Cells) Depends on selection             

Ctrl + -

Delete, (Rows, Columns, or Cells) Depends on selection

Ctrl + Shift + $

to format a cell to currency

Ctrl + Shift + %

to format a cell to percentage

Ctrl + Shift + 1

to format a cell to number

Distributing data across columns

To split text in a cell across column

  1. Select the cells you wish to affect.
  2. On the Data tab, in the Data Tools group, click Text to Columns.
  3. In the Wizard, select Delimiter, then click Next.
  4. In the Delimiters area, check Space, then click Finish.

To combine data from two columns

 “=A2&B2” (where A2 and B2 are the cell sources)

Plus to insert a space between these two composed value, the formula would look like: =A2&” ”&B2

Trimming cells

If you have data that contains additional spaces, there is a function you can use to trim them out.

To remove extra spaces from data:

  1. Select a destination cell for your formula.
  2. Type =trim([cell name]), then press Enter

Transpose

When you want to move a column into a row headings

Copy and click on Paste special and tick Transpose

 

Comments

We welcome your thoughts and opinions. Please keep it clean and friendly!