>
0 items - $0.00

Your shopping cart is empty

Why not add some items in our Shop
Call Us at +27 21 786 1224
Home /

Blog

Other categories

General    Basic Excel    Advanced Excel   Excel VBA




Introducing the Concepts of Pivot Tables




What is a Pivot Table? A Pivot Table gives a summary of information. But let me try and explain it as follows. Transaction Description Debit (in R100) Bakery 50 Bakery 10 Juice 7 Dairy 20 Dairy 10 Dairy 30 Meat 10 Meat 30 Tea & Coffee 40 Dry Store 20 Dry Store 50 Fruit & Veg 30 Fruit & Veg 30   If you look at these transactions and I ask you how much was spent on Diary. If you add together 20 + 10 + 30 = 60 (i.e. R6000) you indirectly already know what a pivot table does at […]



0 comments




Tips and Tricks 5: Excel Shortcuts – Entering the current date and time




  You might want to insert the current date or time in Excel. Two easy shortcuts for this is:  Ctrl + ; is a shortcut key  to enter the current date in a cell. Ctrl + :  or  Ctrl + Shift + ; are shortcut keys to enter the current time in a cell. It should be noted though that once you have entered a date or time, these values are fixed. Should you wish to enter a date or time that will constantly update each time that the Excel file opens or when Excel refreshes calculations, you will then rather want to […]



0 comments




Tips and Tricks 4: Excel Shortcuts – Cut/Copy/Paste/Undo/Redo and and fast sheet navigation




Copy, Paste, Cut Ctrl + C is a shortcut key to copy; alternatively click on the Home tab, Clipboard, and Copy. Ctrl + V is a shortcut key to paste; alternatively click on the Home tab, Clipboard, and Paste. Ctrl + X is a shortcut key to cut; alternatively click on the Home tab, Clipboard, and Cut. Undo and Redo Ctrl + Z is a shortcut key to Undo the previous key stroke. Ctrl + Y is a shortcut key to Redo or restore the previous Undo. Moving to the next or previous window Ctrl + Tab is a shortcut […]



0 comments




Tips and Tricks 2: Excel Shortcut to select a Region or a Current Region




Excel shortcut to selecting a rangeNormally when selecting a range, one uses the mouse to drag over the area that you wish to select. However, when there is a contiguous range of values you would like to select, first make sure of the following before using the shortcuts. The range is surrounded by an empty rows and empty columns (or the row or column headers to the left and the right). Now click in the top left corner of the range you want to select to activate that cell. Next, press Shift + End + → which will select the row area […]



0 comments




Tips and Tricks 1: Excel Shortcut to select a Row or Column




Excel Shortcut to select a Row or Column: To select a column of an active cell, press Ctrl + Spacebar or click on the column border. To select a row of an active cell, press Shift + Spacebar or click on the row border. Excel Shortcut to hide a Row or Column Ctrl + 0 hides the column of the active cell.  Ctrl + 9 hides the column of the active cell However, I found that these keys only work when pressing the 0 above the Qwerty keyboard. Those in the numerical pad does not work.



0 comments




What if Excel see Dates in your Bank Statements as text? Convert text to dates in under a minute.




I have a bank statement which I download every month as a csv file. The date is given in format 01/12/2015 (or dd/mm/yyy – day/month/year) format. Excel reads this as text.Here is how you convert Text to Date. Select the Column where the dates are listed. Click on the Data tab. In the Data Tools Group, click on Text to Columns button. In the Convert Text to Columns Wizard:Select Data Type as Delimited text. Click Next Selecting the Delimiters: None is necessary since you are working with a single column of data anyway. Click Next Selecting the Column data format […]



0 comments




What if Excel see Dates in your Bank Statements as text? Convert text to dates in under a minute.





0 comments




Multi-user prevention on Dropbox




Often we sit with the situation where we have multiple users adding data to workbooks. We want to prevent more than one user to open the same workbook so that one person does not overwrite the changes of another. When we are on a network, the code below will work. But this is not the case in Dropbox. Private Sub Workbook_Open() If ThisWorkbook.ReadOnly Then MsgBox “This File is already in use. ” & Chr(13) & Chr(13) & “Multiple users are not allowed at one point in time.” & Chr(13) & Chr(13) ThisWorkbook.Close savechanges:=False End If End Sub Shared folders in […]



0 comments




Sending email from Outlook using a non-default account




My mentor David Wood from AdvancedApps has helped me out with code on how to send email from an Outlook Account where this Account is not the default Account. Outlook 2016 – Select the account from where email must be send. I am trying to send mail from a specific account. This account in Outlook is not the default account. In this case what makes it much easier is that the user can select the email address rather than account number. sendCaller loops through the accounts until it finds this email address. From there on it will call sendFile from […]



0 comments




Easily moving cell contents to a different location. No Cut and Paste needed.




Easily moving cell contents to a different location. No Cut and Paste needed.   Sometimes we built a list in Excel and we want to move some cell entries up or down according to our preference. As an example, say we have the months of a year: January February March April May June July August September October November December Now we might want to have the months starting at March, as it is considered the start of a financial year in some countries. Often we would do as follows: We will select the range that we want to move, right […]



1 comment




Excel Advanced Data Validation Technique




Excel Advanced Data Validation Technique Does your drop down list depends on the selection from another drop down list? Below we will discuss an Excel  Advanced Data Validation Technique showing how to create a drop down list which depends on the selected item from another drop down list. I discuss two approaches. The first of where we stack the lists in one column and the other where we follow a more complicated path. I will explain in both cases what needs to be considered. We are developing a range of bicycles where each bicycle is manufactured at one of various factories located in three countries […]



1 comment




Why should I want to take any of the Excel Training Courses?




Why should I want to take any of the Excel Training Courses?   Microsoft Excel is the most widely used spreadsheet program today. It offers a host of features and capabilities of which many users remain unaware. It is seldom that a user without formal training fully utilize all the features of Excel. Below I give reasons for considering some Excel Training Courses and who will benefit from this. I refer here to an Article in MarketWatch written by Jeremy Olshan. He reports that “Close to 90% of spreadsheet documents contain errors…”. Excel can be a dangerous tool to the inexperienced […]



3 comments


Leave a comment

Your email address will not be published. Required fields are marked *