How do I insert a row in Excel?

How do I insert a row in Excel?

How do I insert a row in Excel? Link to YouTube video If you already have a spreadsheet of data and you wish to add more data in the middle, instead of the bottom of the table, then you're going to need to know how to insert a row in Excel.  This is very quick and easy to do, and there are a couple of methods.  We will even show you how to insert multiple rows or columns at once - even some intermediate Excel users don't know about this technique, so sit back and get ready for some more adventures in Excel. So let's start off with a simple table, we have 6 teams in rows 2 to 7, and they're listed in alphabetical order.  Then we realise that we missed Tim Talbot from Tauranga off the list!  So to keep this in alphabetical order, we will need to insert this new row between Otago and Wellington (rows 6 and 7).   You need...
Read More
How do I total a column in Excel?

How do I total a column in Excel?

How do I total a column in Excel? Link to YouTube video So this is it, you've come to the stage where you're no longer just using Excel to enter data, but just beginning to start tapping into the incredible power of the program.  And the good news is, it's very easy! To total (add up) a column in Excel you will need to use a formula called SUM; there are two ways of doing this, the easy way and the extremely easy way.  We'll begin with the extremely easy way and then build up to the easy way gradually so you get the idea of the basic principle behind Excel formulas.  By the way, this will also work the exact same way in Google Sheets. So let's begin with our very simple example, we have a column of numbers, they are all in column A, and the range from row 1 to 6:   Getting the sum total of the column is as easy as...
Read More
How do I freeze a row in Excel?

How do I freeze a row in Excel?

How do I freeze a row in Excel? Link to YouTube video Freezing rows in Excel is very useful if you have more than "a screen's worth" of data; i.e. if you scroll down it is helpful if you can still see the column headings when you are further down the data set. Although without a bit of direction, it can be frustrating trying to freeze a row (know as Freeze Panes in Excel) if you don't know exactly where to click, as it may not be where you expect. In the example below, freezing the the row is easy because the header row is in row 1. All we need to do is click on the View ribbon, then click the Freeze Panes button, and then click Freeze Top Row: But let's say for example there are a few title rows or other data above the table, and the header row is not in row 1. Now we need to tell Excel in another...
Read More
How do I lock cells in Excel?

How do I lock cells in Excel?

https://youtu.be/lvzB9FK1z6o Link to YouTube video How do I lock cells in Excel? If you have created an Excel workbook with calculations in it that you don't want your colleagues to be able to accidentally delete or overtype, you will need to lock the relevant cells.  You may be surprised to hear that by default, all the cells in Excel are locked by default!  But if that is the case, then why can they be edited?  That's because it is a two stage process. So let's imagine you've made this sweet spreadsheet at work, and you want your colleagues to be able to update the cells A3 to B8 (the cells in white) but have the green cells locked down: As I said, by default all the cells are actually locked already, what you need to do now is select the cells that you want to be unlocked, i.e. in this case cells A3 to B8.  Highlight and select this cell range, then right-click and select...
Read More
How do I merge cells in Excel?

How do I merge cells in Excel?

Link to our YouTube video So how do I merge cells in Excel?  This is another basic technique in Excel that gets a lot of searches on Google, so you're not alone if you're struggling with this one! Let's say we have a table and we want to make one of the cells merge across the top of the subheader cells (like here we want the year to span across the whole table):   We need to highlight from cell A2 where the year is across to cell E2 by clicking in cell A2 and dragging the mouse across to highlight all the cells we wish to merge:   Then, in the default "Home" tab of the ribbon, click on "Merge and Centre":   And BAM, your cells are merged:   This only works how you'd expect if the rest of your cells are blank.  Excel only keeps the upper-leftmost data in a range if you have data in more than one of the cells, so use this with caution.  ...
Read More
How do I Add a Column in Excel?

How do I Add a Column in Excel?

How do I add a column in Excel? Link to our YouTube video How do I add a column in Excel? - This is a really simple, but apparently it is a commonly searched for problem!  So if you've found this article because you can't add a column then you are not alone. First of all, select the entire column to the right of where you want to insert the new one.  For example, if you wanted to insert a column between columns B and C, then you would click in the grey area at the top of column C (see image below for where you need to click to select an entire column): Once you have selected the column, you then need to right-click in that same place to come up with a context menu, then click "Insert". And that's that, the new blank column appears, what was in Column C now becomes Column D: The exact same technique is used to insert new rows,...
Read More

Combining Excel Workbooks

You may have several Microsoft Excel files that are used to control a particular area of your business, and you may be thinking to yourself, "how do I combine all these Excel workbooks into one?"  You can move or copy tabs (excel sheets) between workbooks very easily with a few clicks: Link to our tutorial on YouTube Step 1:  Open all the workbooks you want to combine. Doing this makes it possible to copy or move the tabs between the workbooks. Step 2:  Decide which of your workbooks is going to be your new "main" workbook. Or if you want to create a new "main" workbook, create a new blank workbook now, and save it with a filename such as "Workbooks combined.xlsx" or whatever is suitable to you. Step 3:  Now select a workbook which is not your "main" workbook. Now you have selected another workbook that is not your "main" workbook, you are ready to move or copy the relevant tabs over to your main...
Read More

Formatting Cells in Excel – The Basics

We have uploaded a video to YouTube about formatting cells in Excel. This is a great video for beginners who need a good basic overview of how to format cells in Excel. In this video we go into detail about formatting cells in Microsoft Excel, using colours to highlight , fonts, bold, underlined etc., number formatting, formatting tables, using cell styles, using the format painter, and cell borders. https://youtu.be/1I1UuFZZV8I   Link to You Tube Video...
Read More

1st Product Launch! Timesheet Invoices workbook

We are pleased to announce that our first "off the shelf" workbook is available for sale on instant download. We have a demo video on YouTube and also a simplified free version. This is an Excel Workbook designed for self-employed contractors who charge their client at an hourly rate and send their invoices weekly. It is very easy to use and is protected so that none of the formulas can be accidentally deleted. Within the file there is a link to an instructional YouTube video so you can quickly learn how to use the workbook. The workbook consists of a summary sheet and an individual timesheet tab for each week of the year. The summary sheet details all the hours, labour value, mileage, and expenses by week, and summarises the total value by month and a grand total for the year. This is the Instant Download version containing the standard version of the workbook, if you need the workbook to be customised then...
Read More

How to link between two workbooks (files) in Excel

Often we need to summarise information from one (or many) Excel files into another.  Doing this is very easy; to begin, you will need both of your files open; the file that you want to retrieve the information from, and the file you want the information to go to.  If you open each workbook side by side on your screen it makes this process the easier.  Select the data on the sheet you want to copy from, copy it, then in your destination workbook select where you want your linked data to go, right-click with your mouse and select the "Paste Link" icon: You will notice if you select a cell in your destination workbook that it now contains a formula that refers to a corresponding cell in your source data workbook.  Here you can see that cell F2 is referring to Workbook 1.xlsm, Sheet1, cell F2: If you make any changes to your source data workbook, they will be automatically changed...
Read More