Excel Tips

Excel Tips for Supply Chain Professionals and for everyone who use MS Excel.

More excel tips will be added to this page occasionally.

Come back again to see if anything useful for you has been added since your last visit.

Tip #17

This excel tip can be used to see the result of the formula you have been typing. It can be very useful if you are not sure when you are making a formulation, you can see the result live and so you can validate it while you are still working on it. It can be useful for long formulas especially.

excel tip F9 formula

Tip #16

You want to vlookup, but the data you need is on the left side of your lookup column! Don’t have to revise the file! Just use the formula in the photo.

excel index match formula excel index formula, excel match formula, excel tips


Tip #15

This can be used when you need some numbers to be rounded to hundreds or thousands or just multipliers of 10.

You can also use roundUP or roundDOWN.

The formulas given in the photo are for the cells marked with stars.

Excel tip for round formula in excel


Tip #14

This is a very simple yet time saving tip.

When you start typing a formula in a cell starting with “=” and you type first letters of the formula, excel shows you the possible selection of formulas with a drop down list. When you select the related formula with arrows in the keyboard, you just press “tab” in the keyboard and it automatically completes the formula name so you can continue writing the formula not even touch in your mouse. It’s so cool.

Excel tip for formula autocomplete in excel

 


Tip #13

This one is for jumping to the beginning or end of a column with the keyboard shortcuts.

excel tip to jump to bottom or top cell in a column


Tip #12

If you sum up values in excel very often this tip can be very useful for you. This is the short cut how you get the sum of a column.

You just select the 1st empty cell at the bottom of the column and click “Alt”+”=” that’s all.

Excel tip to get sum of a column in excel


Tip #11

This excel tip is the short cut for transposing data.

When you press ALT+E+S you can open the table of options for “paste special”. The transpose option is activated by “E” and “V” is for pasting values only.

You might as well choose to paste the same formatting or formulas by using their short letters shown in the table undercrossed in the table.


Tip #10

This excel tip is for the ones who got bored to use the same comment box for years.

First you have to add “Change shape” icon to your quick access bar as shown in the picture. After this just select the comment and click “Change shape” to see possible options which are so many! After changing the shape of it, you can right click and click “Format comment” to change its color and font, etc. Super cool!

This week's excel tip is for the ones who got bored to use the same comment box for years. First you have to add "Change shape" icon to your quick access bar as shown in the picture. After this just select the comment and click "Change shape" to see possible options which are so many! After changing the shape of it, you can right click and click "Format comment" to change its color and font, etc. Super cool! Note: This works for Excel 2010 #excel #office #cubicle #work #job #cubiclelife #tip #show #artofsupplychain #supplychain #supplychainmanagement #planning #businness #industry #management #transport #transportation #shipping #warehouse #warehousing #logistics #forklift #ship #container #supply #demand #forecast #materials #career #inventory

A photo posted by Art of Supply Chain (@artofsupplychain) on


Tip #9

Some keyboard shortcuts that can be very helpful once you get used to them.

Excel useful keyboard shortcuts


Tip #8

This excel tip is for selecting whole table with keyboard shortcut. Just select any cell in the table and then press Ctrl+* , this is all.

Excel tip to select table at once


Tip #7

This excel tip is for filling the blanks in a table. This can be useful when you get a report from a software or when you make pivot tables and you want to fill the blanks.

You select the range first and then press “Ctrl+G” which means”Go to”.

From the window which opens you select “special” and under this you select “blanks”. It will automatically select the blank cells in your range starting from the first one. You just refer this first cell to the one above it bu writing the formula like in the video and click “Ctrl+Enter” . Ctrl+enter makes it apply this formula to all selected blank cells and so you are done filling it.

Today's excel tip is for filling the blanks in a table. This can be useful when you get a report from a software or when you make pivot tables and you want to fill the blanks. You select the range first and then press "Ctrl+G" which means"Go to". From the window which opens you select "special" and under this you select "blanks". It will automatically select the blank cells in your range starting from the first one. You just refer this first cell to the one above it bu writing the formula like in the video and click "Ctrl+Enter" . Ctrl+enter makes it apply this formula to all selected blank cells and so you are done filling it. Hope this helps you. Tag your friends who might find this helpful. #artofsupplychain #supplychain #supplychainmanagement #planning #hardworking #workhard #businness #industry #management #planning #transport #transportation #shipping #warehouse #Warehousing #logistics #forklift #ship #container #supply #demand #forecast #excel #exceltip #office #tbt #work #ilovemyjob #lovemyjob

A video posted by Art of Supply Chain (@artofsupplychain) on


Tip #6

This excel tip is the flash fill function. You may want to view the data you have in a different format. İn this example I show how to easily modify “surname, name” format to “name surname” format. You just need to show it 1-2 examples and then click data->flashfill after selecting all range and it will automatically fill in using the same logic. This is also capable of doing similar thing with numbers so you can convert phone number list in a standart format that you want to view!

Excel tip to flash fill data in excel


Tip #5

This excel tip is for easily inserting a screenshot into your excel sheet. Try using the “Format” menu for coloring your screenshot (like the filters in instagram) and you can also apply various “Artistic effects”. Also if you select one of the web pages to paste in your excel sheet, it can save the hyperlink so when you click on the picture it opens the web page it refers to.

How to insert pictures in excel and how to apply artistic effects


Tip #4

This excel tip is an advanced use of one of the older tips. This is very useful when you need to have some numbers put in a standard formatting including letters (or other characters).

This kind of function can be used when you need to upload some data in the system, etc.

excel tip to change format of numbers in a standart way


Tip #3

This one is about selecting entire column or row with keyboard shortcuts. After making the selection you can hold shift and enlarge your selection using arrows in the keyboard.

how to select entire column or row in excel


Tip #2

If you double click on format painter, you can paste the same formatting several times for every click. Then press Esc to to deactivate the formatting.

 


Tip #1

This excel function is used when you need to have some numbers formatted so that they have a certain number of digits. This usually happens with product codes in the supply chain excel files. Some systems do not accept if the code has lower digits then it was set to. So you have to find low digit codes and add 0 character(s) at the begging. Now, you don’t have to do this, but only change the format setting of the cells at 5 second.

Share this on...

Leave a Reply

Your email address will not be published.