Excel Tips for Improving Productivity Using Excel

Sponsored Links
  • To remove grid lines from excel worksheet, go to menu > tools > options > and un-check grid lines option. (Excel 2007: office button > excel option > advanced) 
  • To hide a worksheet, go to menu > format > sheet > hide 
  • To freeze rows on top, select the a row and use menu > window > freeze panes 
    Sponsored Links
  • To disable annoying formula errors, go to menu > tools > options > error checking tab and disable errors you don’t want to see.
  • To change the shape of cell comments from rectangle to some other symbol,selects the comment, go to drawing tool bar and change the shape from there. 
  • To transpose a range of cells, copy the cells, go to empty area, and press alt+e+s+e 
    Sponsored Links
  • To save data filter settings so that you can reuse them again, use custom views 
  • To select all formulas, press CTRL+G, select “special” and check “formulas” 
  • To select all constants, press CTRL+G, select “special” and check “constants” 
  • To clear formats from a range, select menu > edit > clear > “formats”
Sponsored Links

What is Microsoft Excel

Microsoft Excel is a program developed by Microsoft and it offers a spreadsheet application for users. This spreadsheet application is designed for use in Windows and Mac OS computers and devices. This application has been widely used in all its components or platforms right from when version 5 was released in 1993. This spreadsheet application has replaced other software products like the Lotus 1-2-3. It is part of the Microsoft Office program. The main components of Microsoft Excel are;

  • Calculation functions
  • Pivot tables
  • Graphic tools
  • And Visual Basic for Applications, a macro programming language 

Top features and benefits of Microsoft Excel spreadsheet

Sponsored Links

Microsoft Excel spreadsheet helps build great charts. This application helps businesses unlock their potential data. Businesses are able to make use of different formulas in a grid of cells within a table. The required data is put into individual cells when it is required in rows or columns. It allows users to sort, filter, and display the rows and columns in visual presentation. Users can use things like graphs, charts, clustered columns to add more meaning to data.

Visual presentation has a big impact in the tabulation and comprehending of data. The visual presentations like graphs can help readers capture the content within a data more easily. Using the visualization features, it helps put some more emphasis on business reports and business marketing materials. Charts are recommended for any type of data which needs to be presented in either X or Y-axis.

Sponsored Links

With Microsoft Excel, you can use conditional formatting to put more emphasize on certain information. Users are able to format the spreadsheet with use of features like italics, bolds, color shades, and other functions in order to highlight the most important data. The conditional formatting is helpful in businesses when they present accounting information such as the balance carried forward by a company or a pre-tax profit. This will put emphasize on that information contained in the sheet. Besides, users are able to select a suitable color scheme, which suits their preferences by going to the  “Quick Analysis button” and then selecting the Formatting tab.

When using Microsoft Excel spreadsheet, it can help businesses identify trends. With the presentation of data in graphical form, it helps include aspects such as average lines, which are essential in showing the key trends derived from the information presented in that graphic form. This is something, which can be applied to emphasize key points to other users or readers of the information in a straightforward way.

Sponsored Links

Moreover, Microsoft excel allows users to bring their data together. With these spreadsheets, users can put all the information from different documents in one place so that they are maintained within one single location for easy access. The raw data presented in the sheet contains all the content even from other spreadsheets meaning that users are able to import texts or images when they want. Besides, users can add objects with use of the “Insert tab” or by using the additional spreadsheets.

As part of the Microsoft’s Office 365 productivity suite, the Excel spreadsheet is available online and this implies that businesses can have access to the program using their computers or smartphone devices from anywhere. This can help business leaders and employees accomplish data tabulations even when they are out of their offices or at home. As long as the users have an internet connection, they can use their devices whether PCs, smartphones, laptops, or tablets to access the Microsoft Excel spreadsheet. This makes it easy for remote and mobile working when on the go.

Sponsored Links

What are the disadvantages of Microsoft Excel?

This software when installed in computers requires frequent updating, and this is time consuming. When the software starts updating, it may take a lot of time. Not all computer users have the program which is compatible to view the documents that have been created using Microsoft Excel.

In essence, Microsoft Excel provides users with different formulas, which they can use to ease their calculations, something that saves time and enables them calculate difficult problems easily. The problem of using this program is that it limits users from learning or understanding the knowledge of solving problems manually. They have to rely on the spreadsheet to solve their calculations and present the data rather than doing it manually.

The Different Excel Formulas

Using Excel formula function helps you create and apply various formulas including sum, count, trim, and others. With this Excel formula, it is intended for use by those people with no experience or those who have some little knowledge on how to work with spreadsheet programs including Excel.

Sponsored Links

1. SUM

Formula: = SUM (6,6) or  =SUM (A1, B1) or =SUM(A1:B5)

Using SUM formula, it helps find the total sum of two or more numbers. The SUM formula gives you the total sum of two or more numbers. The different numbers you are adding together are separated by commas, and the SUM function will add them together. In addition, you can have cell references of the numbers you want to add, and the SUM formula will add the numbers in those cells. Besides, the SUM formula can add numbers within a range of cells that are separated by a colon between the cells.

2. COUNT

Formula: =COUNT (A1:A10)

You can use the count formula to count the number of cells which have a numbers in them within a certain range

3. COUNTA

Formula: =COUNTA (A1:A10)

One thing with the COUNTA Formula is that it works with different data types. It can help in counting all numbers of non-empty cells within a certain range. It counts that if there are cells with numbers or other characters in them

 

 

counta

4. LEN:

Formula: =LEN (A1)

Using the LEN formula helps you count all the numbers of characters within a given cells range. In this case, spaces may also be counted.

=LEN (A1)

 

Note: You have 10 characters where there is no space between words and 12 characters where there are spaces between the words.

5. TRIM

Formula: =TRIM (A1)

Sponsored Links

This formula is particularly useful in situations where you have to pull data from the databases. This formula compares with either the use of “VLOOKUP’s”or “IF statement”.

=TRIM (A1)

6. RIGHT, LEFT, MID formula

Formulas: = RIGHT(text, number of characters), =LEFT(text, number of characters), =MID(text, start number, number of characters).

The formulae used above returns certain number of characters which are mentioned from a specific text string.

Points to note:

RIGHT will give you the number of characters which are from the right side of the text string.

LEFT will give you the number of characters which are from the left of the text string.

MID will give the number of characters required from the middle.

7. VLOOKUP

Formula: =VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)

Sponsored Links

This formula will first look up for a value in table that is in leftmost column, and it then returns the value in the same row, from the value of a column you specify. If you use any number for the lookup value, the formula makes it easier to ensure that the data you are getting back is a correct match.

 

8. IF Statements

Formula: =IF(logical_statement, return this if logical statement is true, return this if logical statement is false)

Example: =IF(C3>D3, “Met Quota”, “Did Not Meet Quota”)

 

9. SUMIF, COUNTIF, AVERAGEIF

Formulas: =SUMIF (range, criteria, sum range), =COUNTIF(range, criteria), =AVERAGEIF(range, criteria, average range)\

 

10. CONCATENATE

Sponsored Links

This is the process of merging or combining two different cells to form one cell.To find all the required right Excel formulas for a job, you will discover that there are close to 316 predefined or built in function within Excel.

concatenate

Solutions to Common Print Problem When You Use Microsoft Excel

There are different print problems experienced when you use Microsoft Excel but we will present solutions to some of the problems as outlined below;

  1. Microsoft Excel prints only a portion of the worksheet
  2. I need to print one page wide or tall of my sheet
  3. The footer or header of the printed sheet does not look the way in wanted
  4. Why is Microsoft excel ignoring page breaks I have set
  5. Some columns and rows are printing on the wrong page
  6. Excel is not printing multiple print areas on same page
  7. I am not able to scale a chart to print to a desired size or I am not able to print multiple charts on one page

ADVERTISEMENT

Microsoft Excel prints only a portion of the worksheet

Sponsored Links

If Excel is only printing a portion of your worksheet, you need to check whether a print area has already been defined. If there is a print area already set, then Microsoft Excel will only print the print area. To check whether a worksheet contains a printing area, you should go to “Page Break Preview” located on the “View” menu. The areas you see of the worksheet in a white background are the parts that will appear in a print. In order to print the whole sheet, you need to clear the print area by going to the “File” menu, then to “Print Area” then select “Clear Print Area”

You can also adjust the print area by changing its size to include or exclude rows and columns. You do this by going to “View” menu and then clicking “Page Break Preview”, where you then adjust the borders surrounding the print area by dragging them. If you want to include nonadjacent cells to the printable area, you need to select the cells then right click any cell within the selection, then click the “Add to Print Area” button found on the shortcut menu..

I need to print one page wide or tall of my sheet

If you want your excel sheet to print one page tall or wide, you need to prevent the columns or rows from spilling over the edges of the page. This is done by shrinking the excel sheet in order to appear in one page tall or wide. To do this, you go to “File” menu and click “Page Setup”, then click  “Page” tab. When you are at the “Page” tab, you click “Fit to”, and type in “1″ in the pages wide box or tall box. This action deletes the number from the other box. 

The footer or header of the printed sheet does not look the way in wanted

If the header or footer you have printed does not look the way you wanted, you can solve this problem by choosing from the various built in footers and headers to create your own. You can go to “View” menu and click “Header and Footer” where you choose black and white only. The footers and headers text will always be black meaning that it cannot be printed in color even when you are using a color printer.

Sponsored Links

In case you want to repeat linked information or graphics on top of every page, you can use print titles instead of using print headers. This feature can be used to print a company log or address at the top part of every page of the sheet. You place the company information on the first few rows within the worksheet. Then you go to “File” menu, and click “Page Setup”, then next click the “Sheet” tab, and on the “Rows to repeat at top” box, you enter the rows that contain the company log and the address. 

Why is Microsoft excel ignoring page breaks I have set

If Microsoft excel ignores the page breaks you set, it means that your workbook has been set in such a way that Excel fits the printed work on a certain number of pages thus ignoring the manual page breaks you set. This reduces the size of the printed Excel worksheet. To check whether this is the case, you need to go to “File” menu, and click “Page Setup”, then the “Page” tab and under “Scaling”, check the “Fit to” option.

You use “Adjust to” instead of using “Fit to” in order to fit the worksheet. To go to “Adjust to”, you click “Page Setup” in the “File” menu and then click “Page” tab and you will see the “Adjust to” button.

If you find that the “Adjust to” option does not give you the results you want, you can set a print area by selecting each page as a separate range. Excel prints the worksheet nonadjacent ranges on separate pages. 

Some columns and rows are printing on the wrong page

If some rows and columns are printing on the wrong page, you need to make some adjustments. The row height, column width, page margins as well as page breaks help determine the number of rows and columns that are printed on a page. You can make the margins smaller. If some columns and rows are printing on the next page, you may try decreasing the margins at the top, bottom, left and right.

Sponsored Links

To adjust the margins, you go to “File” menu, then click “Page Setup”, and go to “Margins” tab and click it. You can also adjust the page breaks by moving them before you print the worksheet. To do this, you go to “View” menu, and click “Page Break Preview” and the manual page breaks are displayed in thick blue lines. Usually automatic page breaks will appear as dashed lines. The page breaks can be moved through dragging them up, down, right or left.

Excel is not printing multiple print areas on same page

If multiple print areas are not being printed on the same page, you can specify that separate parts of the excel worksheet be printed on same page. The nonadjacent areas of an excel worksheet are printed in separate pages by default. To change this scenario, you need to bring together the columns intend to print.

In order to print the nonadjacent columns in the same page, you need to select the columns, which you do not want to print and hide them. Once you have selected them, you go to “Format” menu, then click “Column” and then click “Hide”. The same happens with the nonadjacent rows whether below or above the worksheet, which you do not want to print. You hide the rows by going to “Format” menu, then click “Row” and then click “Hide”. After that, you can now set the print area containing all the rows and columns including those, which are hidden. 

I am not able to scale a chart to print to a desired size or I am not able to print multiple charts on one page

Sponsored Links

If you cannot scale the chart to print it in the size you want, you can set the print size. The chart on the chart sheet can be scaled when you want to print. To activate the chart sheet, you go to “File” menu, then “Page Setup” and next click the “Chart” tab. Under the “Printed chart size”, you click the scaling option you need to print. For “Custom”, it means that your chart will print in the size that you create when you drag the selection handles. If you have embedded charts, you need to drag to resize.

On the other hand, if you want to print various charts together on same page, you embed them. It is not possible to print multiple chart sheets within one page. If you want to print two or more charts on the same page, you should create embedded charts, which are then resized to fit into one page.

How to Create ‘User Defined Functions”

With use of visual basic macros or functions, we are able to design custom functions, which are not built in Excel. The functions we develop can then be used or applied in formulas just like other functions that are currently applicable in Excel. In order to demonstrate how this works, we will create the “CardOK” function, which will enable us test the eligibility or validity of a credit card number.

Sponsored Links

The aim of the “CardOK” function is to perform a mathematical checking to help determine the validity of the card number in a specific cell, and this check is known as the MOD10 check. However, the mathematical check does not determine the credit worthiness, the credit available in the card, or whether the number of the card is actual one.

To start, you go to “Tools” menu and then click “Macro” and select “Macros”. Now type the Macro name and in our example, it is “CardOk”. Then click “create” and this will show Microsoft Visual Basic Editor Interface.

Once at the Microsoft Visual Basic Editor, delete all the text you see on that page by holding [Ctrl + A], and then pressing “delete” at the same time.

Sponsored Links

After that, you need to cut and paste the below text on to that page:

Function CardOk(CardNum$) As Boolean

‘Logic statement by Richard Harris – obtained from Deja News
‘Implemented by Chui Tey – obtained from Deja News
‘Concatenation and minor correction by Phil Bornemeier
‘Modified for Excel by www.finance-analyst.com
Dim tmpCardNum$, OddSum, EvenSum, i, OneLetter$, Digit, CheckSum
‘Initialize

tmpCardNum$ = CardNum$

CardNum$ = “”

OddSum = 0

EvenSum = 0

‘Reverse order of cardnum drop non-number characters

For i = Len(tmpCardNum$) To 1 Step -1

OneLetter$ = Mid$(tmpCardNum$, i, 1)

If OneLetter$ <= “9″ And OneLetter$ >= “0″ Then

CardNum$ = CardNum$ + OneLetter$

End If

Next i

‘Add numbers in odd positions

For i = 1 To Len(CardNum$) Step 2

OddSum = OddSum + Val(Mid$(CardNum$, i, 1))

Next i

‘Double numbers in even positions, add up all digits, accumulate

For i = 2 To Len(CardNum$) Step 2

Digit = Val(Mid$(CardNum$, i, 1))

EvenSum = EvenSum + Digit * 2 + (Digit * 2 >= 10) * 9

Next i

‘Add OddSum and EvenSum

CheckSum = OddSum + EvenSum

‘Check if CheckSum divisible by 10

If CheckSum Mod 10 = 0 Then

CardOk = True

Else

CardOk = False

End If

End Function

Next you press [Atl + Q], and this action will enable you quit Microsoft Visual Basic Editor and get back to Excel. Our function is now ready for use, and in order to test its applicability, we format cell A1 for text.

What you do is highlight cell A1 and the press [Ctrl+1], the click “number”, then again click “text” and lastly press “ok”.

Sponsored Links

In cell A1, you enter the below valid credit card number:

4200123456789015

Next in cell A2 you type: =CardOk(A1)

The value you get in cell A2 needs to be “TRUE”

This function can also be accessed on the “Insert” menu, where you click “Function”, and in the “Function Category” tab or box, you select “User Defined”, and “CardOk” appears in “Function Name” tab or box. You then select “CardOk” and click “ok”. A box will pop up where you will enter “A1″ or that cell which contains the credit card number, and lastly you press “OK”.

Sponsored Links

Point to note: Many small e-commerce sites, which are unable to afford online credit card authorization, are using this function(MOD10 check)

 

Shortcut keys for Excel Keyboard

  1. If you want to format any selected object, youpress ctrl+1
  2. To insert current date, youpress ctrl+;
  3. Inserting current time, you press ctrl+shift+;
  4. Editing a cell comment, you pressshift + F2
  5. Repeating last action, you press F4
  6. If you want to autosum selected number of cells, you press alt + =
  7. In order to see suggest drop-down in a cell, you press alt + down arrow
  8. Inserting a new sheet, youpress shift + F11
  9. Editing an active cell, you press F2 (this action will place the cursor in the end)
  10. Entering multiple lines in a one cell, you press alt+enter
  11. If you want to hide current row, you press ctrl+9
  12. In order to unhide rows within a selected range, you press ctrl+shift+9
  13. To hide current column, youpress ctrl+0
  14. If you want to unhide columns in a selected range, you press ctrl+shift+0
  15. If you want to recalculate formulas, you press F9
  16. Selecting data within a current region, you press ctrl+shift+8
  17. If you want to see formulas in your worksheet, you press ctrl+shift+` (ctrl+~)
  18. If you want to change the reference type from absolute to relative and vice versa while editing formulas, you press F4
  19. In formatting a number as currency, you press ctrl+shift+4 (ctrl+$)
  20. If you want to use outline border around selected cells, youpress ctrl+shift+7
  21. Opening the macros dialog box, press alt+F8
  22. Copying value from above cell, youpress ctrl+
  23. Formatting current cell using comma formats, you press ctrl+shift+1
  24. If you want to switch to the next worksheet, youpress ctrl+shift+pg down
  25. If you want to switch to the previous worksheet, you press ctrl+shift+pg up