by Ilker | Apr 18, 2018 | Excel Tips & Tricks
Ever spent time to transpose rows and columns in a data set? It can be a very tedious job to switch rows to columns or columns to rows. It gets even harder if your data is updated frequently. Not anymore, for you can transpose rows and columns in a table using a...
by Ilker | Apr 12, 2018 | Excel Tips & Tricks
In complex models, it is important to write comments explaining how the formulas work. But the limitation of Excel’s Insert Comment feature is that you must type the entire comment in a textbox. There is an alternative. You can use N() formula to insert your...
by Ilker | Apr 11, 2018 | Excel Tips & Tricks
Be it for creating a time card or planning, creating a flexible calendar where you can omit holidays will come in very handy. Using the WORKDAY function you can easily create such a table and incorporate it in your data models. Syntax =WORKDAY(previous date, 1) Steps...
by Ilker | Apr 11, 2018 | Excel Tips & Tricks
Algebraically, the nth root of a number can be calculated by using 1/n for its power. You can always use caret symbol (^) to find a certain power of a number, but doing this with a formula can be easier to read and identify in nested formula sets. Syntax =POWER(base...
by Ilker | Apr 11, 2018 | Excel Tips & Tricks
You can always use caret symbol (^) to find a certain power of a number. However, doing this with a formula can be easier to read and identify in nested formula sets. Syntax =POWER(base number, how many times that base number will be multiplied by itself) Steps Begin...
by Ilker | Apr 11, 2018 | Excel Tips & Tricks
Dissecting and organizing data doesn’t have to be challenging. You can quickly filter your data items with this method using a combination of formulas to determine which are unique items. Syntax =LOOKUP(2, 1/(COUNTIF(expanding unique list, original list)=0),...
by Ilker | Apr 9, 2018 | Excel Tips & Tricks
Working with Excel’s coordinate system makes building data models extremely easy. You can retrieve the column index information of a cell using the COLUMN function and implement in other formulas. Syntax =COLUMN(cell or range reference you want get its column...
by Ilker | Apr 9, 2018 | Excel Tips & Tricks
Working with Excel’s coordinate system makes building data models extremely easy. You can retrieve the row index information of a cell using the ROW function and implement in other formulas. Syntax =ROW(cell or range reference you want get its row number) Steps...
by Ilker | Apr 9, 2018 | Excel Tips & Tricks
One perfect example of automatically cleaning up data in Excel is when working with name fields. You can separate first and last names (or actually any text with spaces) using this method. Syntax =LEFT(Full name, FIND(” “, Full name) – 1) Steps Begin by...
