On UrbanPro

View My Profile       Proud to be a Teacher       Write a Review  

Saturday, May 6, 2017

Upload Macros Enabled Excel Spread Sheet on One Drive Account

One of my blog viewer " Darren Branch" has asked me, how he can put/upload his Macro Enabled Excel Spread Sheet on to his One Drive Account.

 Asked Query:
"I would like to know how to put my Macros enabled excel spread sheet on to my one drive account"
  
Here is the step-by-step process to upload the Macro Enabled Excel Spread Sheet on to One Drive account.

Note: Click on the Image to ENLARGE it.
  1. Login to the One Drive Account, after login screen will looks like as shown below:

  2. Click on the Upload Button
  3. Click on the File option
  4. Browse the folder and choose the Macro-Enable Excel Spread Sheet and Click on Open button
  5. Your file is uploaded now
  6.  
     

Monday, May 1, 2017

Increase Productivity Using Top Excel Keyboard Shortcuts

Read this post to know the Top Excel Keyboard Shortcuts to Increase your Productivity

These shortcuts are easy to learn and are ones that I use almost every day.

Move Between Worksheets


Ctrl + Page Down
Move to the next worksheet to the right.
Ctrl + Page Up
Move to the next worksheet to the left





Move Up, Down, Left, or Right After Entering Data

You can use all of these to enter data or simply move around the spreadsheet.
 

Enter                 
Move down one cell.
Tab                    
Move to the right one cell.
Shift + Enter 
Move up one cell.
Shift + Tab
Move to the left one cell.

Jump to the First or Last Cell in a Worksheet


Ctrl + Home
Move to the upper left most cell, cell A1.
Ctrl + End
Move to the lower right most cell that contains data.

Jump to the Bottom or Top or Side of a Data Set


Ctrl + Up Arrow
Move to the top of the current data set.  If there are empty rows above the current cell but below the top of the data set, you will have to use this a few times to get to the top.



Ctrl + Down Arrow
Move to the bottom of the current data set. If there are empty rows below the current cell but above the bottom of the data set, you will have to use this a few times to get to the bottom.


Quickly Select a Range of Data

Ctrl + Shift + Down or Ctrl + Shift + Right or Ctrl + Shift + Left or Ctrl + Shift + Up
Use these keyboard shortcuts until the desired section of data has been selected.  You can do this for data within a single column or row or an entire data set.

Extend or Shorten a Selection of Data

Shift + Down or Shift + Right or Shift + Left or Shift + Up
Moves a cell or range selection one row or one column to the right, left, up, or down.  This makes it easy to adjust a range selection when you need to remove something like the table headers from the selection.

Select the Entire Current Data Set

Ctrl + * (on U.S. keyboards Ctrl + Shift + 8)
Selects the entire current data set in which you have selected a cell.


Enter the Current Date or Time


Ctrl + ;
Enter the current date into a cell.
Ctrl + :
Enter the current time into a cell.

Enter the Same Value in Multiple Cells at Once

Ctrl + Enter
Select a range of cells or simply many cells throughout the worksheet and then type a value and hit Ctrl + Enter to make that value go into all of the selected cells.

Repeat the Last Task

F4
This will repeat the last task on the current selection.  This is exceptionally helpful when deleting rows and columns.  Delete the first one, select the next one, hit F4, and that's it.

Quickly Create Absolute or Relative References

F4
Use this when entering a formula or function into a cell.  When you get to a cell or range reference in a formula, put the mouse cursor next to that part and hit F4 to cycle through the options of making that reference absolute, with dollar signs, or relative.  If you want to adjust all cell references in the formula or function at once, select all of the contents of the cell before hitting F4.

Note that this cycles through the different variations of absolute and relative; each row and column reference can be absolute or relative separate from the rest of the range reference.  This sounds confusing but play around with it a bit and it should become clear.


Turn Formulas into their Visible Values or Paste as Value

Ctrl + C and then Alt + E + S + V and Enter
This combination of keyboard shortcuts will change the current selection from a formula or function into the visible output that that formula or function generated.

This may not seem important but it will come in very handy.  I use this almost daily, especially when I need to create sample or scratch data.

Notes

These are the top keyboard shortcuts to increase your productivity.  I use almost all of these shortcuts on a daily basis and it makes working in Excel a much more pleasant experience.
I didn't include obvious keyboard shortcuts like cut and paste or save or print because those aren't particular to Excel and you should already know those by now if you are working in Windows.

Friday, January 20, 2017

Arrange and View Multiple Worksheets at Once in Excel

This feature allows you to view multiple worksheets at once from the same workbook at the same time.

 You need to follow beneath simple steps to view multiple worksheets at once from the same workbook at the same time:

  1. Go to the View tab and click New Window



















  2.  Then click Arrange All




















  3. Select either Horizontal or Vertical and put a check next to Windows of active workbook and then click OK.















  4. Result:You will now have two separate views of the same workbook.


















You can view different worksheets in each pane or the same worksheets, it doesn't matter.

Each side is independently controlled. This allows you to avoid flipping between the tabs to compare information.

When you are finished with this view, just close one of the windows and maximize the other one so it fills the Excel window.

Wednesday, January 18, 2017

Count Specific Errors in excel

Learn, How to count the occurrence of a specific error in a Range of cells in Excel.

Follow beneath steps to Count the number of times a specific error appears in a range.

Select the entire range of cells, for which you want to take the count of error occurrence.
 =COUNTIF(A1:A5,"#NAME?")  

This is NOT an array formula.

A1:A5 is the range to check; change this to fit your data.

#NAME? is the error that you want to count. Change this to the desired error and make sure it is surrounded with quotation marks.



Result:
 



Sunday, January 15, 2017

Count the Errors in a Range of cells in Excel

Learn, How to count the number of errors in a range of cells in Excel:

Follow beneath steps to Count all of the errors that occur in a range of cells:

Select the entire range of cells, for which you want to take the count of error occurrence.

 =SUM(ISERROR(A1:A5)*1)

Array formula: you must enter this into the cell using Ctrl + Shift + Enter or it won't work.

A1:A5 change this to your range of data. That's all you have to do to get this to work for your data.


Result:


Thursday, January 12, 2017

Calculate Average Excluding Zeros in Excel

Exclude zeros while calculating the average in Excel. This method removes all zeros from the equation.

We have two methods for Calculation:

Method 1 : For Excel 2007 and later versions: 

Select the range of cells, for which you want to calculate Average and use below function:
 
 =AVERAGEIF(A1:A5,"<>0")  

 A1:A5 change this to the range that you want to average and that's it.

"<>0" is the part that tells the function to ignore cells that have zeros in them.


Result:




Method 2 : For Excel 2003 and older versions: 

If you have Excel 2003 or earlier, you must use this version of the formula. 

Select the range of cells, for which you want to calculate Average and use below function:

 =AVERAGE(IF(A1:A5<>0,A1:A5))

Array Formula - this is an array formula so you must enter it using Ctrl + Shift + Enter.

A1:A5 is the range that you want to average; make sure to change it in both parts of the formula to work with your data.



Result:


If you don't enter this formula correctly, you will see 2.4 as a result.