Category: MS Excel

Categories
MS Excel

SUM function in EXCEL

#sum, #excel, #additioninexcel

Way to do SUM in EXCEL

1. With AUTOSUM Function

You will find this in Home Tab Ribbon -> Editing group -> AutoSum

2. With sortcut => “Alt +”

This will automatically give SUM function and tale nearby range for summation

3. By typing SUM function with proper Syntax

ex. =SUM(A1:A10)

If range is not in squence, then separate is by comma

ex. =SUM(A1:A10, A15)

4. One more quick way directly by cell reference or values with operator +

ex. =A1+A10  (This will add values of A1 & A10)

or

=1+2+A47  (This will Add 1 & 2 with value of A47 cell)

 5. You just cant believe one more way

Select all the values you need to check its SUM, and see on status bar, you will get Answer right there

6. One More way is…there…but let it be secret as of now…will discuss later on. 🙂
Categories
MS Excel

Drop Down List in Excel

It’s always better to use all the proper tool whenever we work with office work in Excel. Specially when we prepare a sheet where other people will fill the data, at that time better to prepare a sheet with validation criteria applied on the sheet.

So today we will learn one validation criteria i.e. Drop Down List.

In this blog I have use video to make you learn easily and quick way.

Do watch learn, learn a new tool and do share your views:

Categories
MS Excel

Work With Images Related To Data in Excel

images in excel

Whenever we have to place images in excel data, it is always troublesome, so today let’s learn some tricks while working with excel.

For example, I want list of Birds with images. And whenever we sort or filter table data the images should also move with data.

So let’s work with this example.

  1. I have following list.

First we select one of the image then press Ctrl + A, it will select all images.

  • Right click on any of the selected image as displayed in following image, select “Format Object”
Right pane will be open to set Picture property
  • Here two things we have to set – first remove click from Lock aspect ratio.
  • Then only you can change size dimensions (Height & Weight) of picture. (I have set Height 0.5” & Width 0.7”)
  • Now you can see the list as per your data. (Here in my sheet I have all row height as 50.
  • One more property we have to check is select all object again, right click and in Format Picture Pane – in property select option “Move and size with cells”.   As per following picture.
  • Now your list is perfectly ready. Try now filtering or sorting of your tables.
  • Your images will be filtered with your data.
Sorted Data
Filtered Data
  • Enjoyed working?
  • Do let me know if you have any query.

Categories
MS Excel Uncategorized

EXCEL Shortcut keys

EXCEL Shortcut keys for daily usage:

Edit Cell F2 or Double Click inside cell
Line Break inside Cell Alt+Enter
Insert row Alt+i and then r
Insert a column Alt+i and then c
Insert new Worksheet Alt+i and then w
Moving from sheet to sheet – Next sheet Ctrl+PgDown
Moving from sheet to sheet – Previous sheet Ctrl+PgUp
Select the active cell range Have active cell inside range Ctrl+Shift+8
Select Row Shift+SPACE
Select Column Ctrl+SPACE
Freeze Pane Alt+w and then F
SUM Alt+=
Current Date For always changing date use function =today() Ctrl+;
Current Time Ctrl+Shift+;
Instant chart of selected Range F11