<

Go Premium for a chance to win a PS4. Enter to Win

x

Excel - Did You Know !

Published on
7,001 Points
801 Views
27 Endorsements
Last Modified:
Shums
I Love Challenges in MS-Excel
Where there is a will there are many ways. Finding one that works for you is the challenge!
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to do.
The latest info graphic lets take a look that are pretty lesser-known to most:

1. So to start with we often save Excel file and we forgets the path of the file we are working. There is very simple Excel Function here, which is called "Cell". Type in any blank cell of that workbook =Cell("filename"), the movement you press enter, it will show you the path of the file you are working on. Pretty simple & short.
Cell-Function.JPG2. Often typing a Function or a Formula, if you want to see the rest of the argument of that Function, start with any Function, for example if I type =vlookup and I want to see rest of the argument, =vlookup(Ctrl+Shift+A), arguments will appear in front of you like this =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup), so you can see what would be complex formula and helps you making the combination, right in front of you.
Vlookup.JPG3. Often typing Formulas or Function, we get mixed up and Errors and we don't know what to do. So let Excel to help us in Formula Auditing. For example, you have 1 in Cell A1, 2 in Cell A2 & abc in Cell A3 and I put formula =Sum(A1:A3)*A3, which will end up with #Value!, now you want to check, why there was error, then navigate to Formula/Evaluate Formula or press the yellow error sign on lower right of the cell and press Show Calculation Step, on the pop window you will see your formula, press Evaluate, it will show you which argument is giving you error, like in my formula its shows error *A3, then press Step In, it will show you why it is error, in my example it says "The cell currently being evaluated contains a constant."
Error-Text.JPGBy this you can see, which part of your formula is functioning and which one is not working.

4. Its often very inconvenient, if you have to go very end of the column of the sheet and see what is in that last column, when you have a vast number of columns. Lets make it easy. For example, I have multiple numbers in one row to few columns, simply type lookup formula with any scientific number, such as =lookup(1E100,1:1) select the whole row of that column and press enter, it will show you the cell value of the very end of the column.
Lookup.JPGEven if you change any number after last column, it will change the last cell value automatically.

5.If you ever come across the spreadsheet with lots of formulas spread across your data? This is one of the hidden function which would make it easy for you to see what is the Formula or Function behind the specific number. For example if I have 11 in E1, 22 in E2, 33 in E3 and in E4 I have formula =Sum(E1:E3), it give me number 66. You can see the formula in Formula Bar, suppose someone wants to hide the formula and sends you any data file, where you cannot see the formula bar like below image
Hidden-Formula-Bar.JPGFor such instances, there is key called "Back Tricky" or "Caret Key" on top of your keyboard after Esc button.
Press Ctrl + ¬ and you will see the formula like below:
Hidden-Formula-Bar-2.JPGIf you press again, it will go back to normal window, where you cannot see the Formula.

6. Most of us knows how to get current date or time in cell by different formulas, but very few knows how to get current date or time in a cell with Keyboards.
  • To enter Current Date in a cell Press Ctrl+; (semicolon ) it will inserts Current Date in your current Cell.
  • To enter Current Time  in a cell Press Ctrl+Shift+: (colon ) it will inserts Current Time in your current Cell.
This works in almost all the Excel versions.

This is my first article, if you already knew some of the above stuff, then please appreciate my initiative.

Hope this simple trick will help you.
27
Comment
Author:Shums
10 Comments
 

Expert Comment

by:Enrique A. Santos KM4ZQC
Best in Class!
3
 
LVL 1

Expert Comment

by:richsark
Very smart person.
3
 

Expert Comment

by:Rahul Roy
Awesome
3
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Expert Comment

by:Jennifer Liu
Great tips!
3
 

Expert Comment

by:ADRIANA P
Great ! Expert !
2
 

Expert Comment

by:Steve Bez
nice ideas!
2
 

Expert Comment

by:abbey Law
Your knowledge of Excel is impeccable. Being new to Excel myself and only just learning the basics, this article is extremely useful. I look forward to your future articles, thank you once again for sharing your knowledge with is!
2
 

Expert Comment

by:Aamir Hussain
A very good cooperative and professional expert.Always give new idea with excellent logic .
2
 

Expert Comment

by:Brendan Wilson
I read the other article that you wrote, and they both have been very useful. Thank you for taking the time to put this information together.
2
 

Expert Comment

by:Judy Deo
Wow this was so simple and I did not know it. Nice to know now and I know for sure I will have many opportunities to use this in the future.
1

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Join & Write a Comment

Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month