INSERT AN IMAGE INTO AN EXCEL CELL WITH A FORMULA!

 INSERT AN IMAGE INTO An EXCEL CELL WITH A FORMULA! 

I want to react to this title with 4-year-old my son’s words: “oh come on, don’t exaggerate like this.” 😊

So, what does it exactly do? What do we use it for?

The IMAGE function has been designed to add an image into a cell from a URL.

Which file extensions does it support?

BMP, JPG/JPEG, GIF, TIFF,ICO,PNG are the extensions that it supports as of now.

Since it is brand new, I want to talk about it in detail:

The function has 5 parameters. Our regular readers here know this but for those with whom our paths have never crossed, I want to reiterate:

In a function, the parameters that with write in [] are optional. But those that are not written in square brackets must be written.

Now let’s go ahead and take a look at our formula:

=IMAGE(source, [alt_text], [sizing], [height], [width])

• SOURCE                  : URL path of the image file, using an “https” protocol
• ALT TEXT (Optional)    : Alternative text that describes the image
• SIZING (Optional)      : Defines the size.
• HEIGHT (Optional)          : Defines the height.
• WIDTH (Optional)         : Defines the width.

.

For example, if you write the formula down below into a cell in Excel,

=IMAGE(https://cdn.ablebits.com/_img-blog/image-function/items/water.jpg; “Okyanus“)

you will see a picture of an ocean.

Let’s say that you have a list consisting of Product Names, Stock Information and Prices. It wouldn’t be so bad to have the images of these products next to them using the IMAGE function, would it? 😊

A not-so-famous philosopher (That is no one but me😊) once said: “ Visualizing the data is just as important as preparing it.”

.

.

The CONCATENATE function

The Concatenate function concatenates texts in different cells and allows you to write them in one single cell. Texts can be edited in a very flexible way with the Concatenate formula.

In this manner, you can freely change any field you want in the formula and get the desired result.

The arguments of the formula consist of successive textual expressions.

=Concatenate(text1, text2,…)

You can select the texts to be concatenated from the related cells one-by-one.

In the example bellow, the address is created by concatenating the Strict, Street and Number. When the A2, B2, and C2 cells are concatenated directly, it created the MerkezAyazma37 result.

To see a more familiar result like Merkez Strict Ayazma Street Number:37 (this is the Turkish way of writing an address), what can we do?

You can write textual expressions like “Street”, “Strict” into the formula with the long method.


*When you need to write a text with the keyboard into the formula, you need to write it into a double-quote (“ ”). Using texts in ” ” in Excel is pretty strict.

A more efficient method for it to be reusable, editable and controlled centrally is to write strict, street, number information into a cell and use that cell as a reference.

Click here to take a look the the details of the Concatenate formula.

In addition, it to possible to concatenate without using a function. You can use the & symbol as a concatenation operator. It works similar to the concatenate function and allows you to concatenate the texts directly.

We have learned how to concatenate texts above. In addition to this, we might want to separate concatenated texts as well.

You can separate texts written in one cell into separate cells with the Convert Text to Columns function. So, it works the opposite way of the Concatenate function. Click here to read our article about Convert Text to Columns.

Explore the skills of Go To Special!

The function that allows you to detect all cells that contain special data like comments, formula, space, and data validation in your Excel list is the Go To Special function.

With this formula, you can select the cells with formulas at once and encrypt them with a formula; select the blank cells at once and highlight them; select the cells that contain numbers, text, or conditional formatting all together and execute the action you need.

This function is located in the Find&Select button in the Editing group in Home tab.

You can also press F5 (you might need to press the fn key as well) and choose Go To Special in the window that pops up.

Explore the skills of the Go To Special window and facilitate your work!

Let’s think about a scenario where we will fill the blanks in the Satış Bölgesi (Sales Region) column with the correct data like shown above.

First, we need to select the blank cells and then fill in the correct the quickest way possible!

How can we fill these cells easily without having to type into each of them one-by-one?

We will execute this action by using two features together.

Action Steps

  1. First, select the column that has the blank cells.
  2. The F5 shortcut (You might need to press the fn key) –> Go To Special
  3. Select Blanks in the window that pops open and click OK.

Then, we get the look below

You will see that the selected cells are gray and the cell on top is white. You can write into this cell directly. Which means that you don’t have to click to cell to be able to write. The reason why we focus on this is because the areas you want to work on are already selected, so no mouse click is needed. When you click a selected area with the mouse, it cancels the selection so you should be careful about it.

  1. We will write =G2 (the address of the cell right above it) through the keyboard directly. This way we get to say that we will use the data above to Excel. The most important point: Yes, we will write the data above but how is this formula work in the whole column?

    We move onto the fifth and last step for this action.
  2. For the =G2 action to work in all the selected areas of the column, we should press the CTRL and ENTER keys at the same time. After these steps, the Sales Region column will be filled completely.

    Other Go To Special Articles

    Take a look at this article to detect and encrypt the cells with formula by using the Go To Special window. And take a look at this article to learn which action to take to convert Blank Cells to a fixed text.

Introduction to Digital Forms with MS Forms

Communication with your employees and customers, and tracking and analyzing it  play an important role in determining your strategies. You can quickly prepare and share e-form with MS Form while digitalizing. You can take a look at this article to read business scenarios with MS Forms and practical examples in detail.

Your forms that you work on by logging it with your user information are stored in a secure environment. Since you keep your forms in a digital environment, every action you take is saved immediately. For this reasons, your forms don’t get lost or deleted.

10 Outstanding Skills of MS Forms:

  1. The form can be previewed on the mobile phone or computer.
  2. You can receive an e-mail notification when your form is responded.
  3. You can add rules to your form. With these rules, you can change the questions according to the answers given to the questions. Thus, the question displayed by the user changes according to the answer given by the user.
  4. Answers can be analyzed by question-based or person-based custom charts.
  5. Responds can be viewed on the Excel list.
  6. The background color or picture of the form can be customized.
  7. When you delete a form, it goes to the recycle bin. Thus, you can activate the form for reuse whenever you want.
  8. You can make forms with points. You do not have to show the scores to the person who answered the form. When you want to show it, you can show it as soon as the form is answered.
  9. The user can be allowed to respond the form multiple times.
  10. The questions can be mixed within themselves. The options of the questions can be mixed within themselves.

MS Forms Screen

You can start creating your own form by click the New Form button here. The forms shared with you or group forms can be viewed in this screen.

Create a Form

We can divide a created form into 6 main topic.

  1. Questions
  2. Answers
  3. Preview
  4. Theme (Design)
  5. Share
  6. Setting

We can start by naming the Form first. You can add your company’s logo or an image suitable for you company’s concept through the add image button right next to the name of your Form.

Add Form Elements

The questions type you will add to your poll with the Add new button look like this:

There are 4 standard question types.

  1. Option
  2. Text
  3. Rating
  4. Date

You can view more question types by clicking the arrow button on the right corner. These are other question types like Ranking, and Likert.

You can add an image for the form element you have added.

The buttons in the upper right corner are:

  1. Make a duplicate of the question (default)
  2. Delete the question (default)
  3. Change the order of the questions (default)

In the lower right corner:

  1. For questions with options; allows markings multiple options
  2. Requires the question to be responded (default)

If you think that you have completed the form when you are done adding the questions, you are ready to share it.

It is enough for you to click the Share button on top right and copy your form’s link  and then send this link to the people you want.

Even if you share your form, any change you make is active all the time. People who you shared it with always see the latest version of your Form.

 

XLOOKUP: THE GAME CHANGER

THE XLOOKUP FUNCTION

Last you on August 29, two new important functions were announced: XLOOKUP and XMATCH. In this article, we will be talking about what the XLOOKUP function is, when and how to use it. When you start using this function, you will just not be able to let it go.

I want to mention a few things about VLOOKUP first: as you know, the VLOOKUP function was indispensable for many users, even those who didn’t know wanted to learn it. And even in the 7 Most Used Excel Functions presentation Microsoft prepared, the first function is VLOOKUP.

As dispensable as it is for some users, it was useless for some others. Because VLOOKUP required some conditions to run and the function returned us the first match it found. On top of that, it decreased Excel’s performance since it could cause unnecessary calculation in the stated table range. It is the function for those who have unique data, but in a table with duplicate data -i.e. recurring data-  it wasn’t very useful since it didn’t give us all the records. Of course, there are some methods to list all the records, but you either had to solve the issue by using auxiliary columns or list all the records with the Array Formula.

LONG LIVE THE DYNAMIC FUNCTIONS!

Fortunately, in September 25, 2018 the new dynamic array functions were announced and we got to breathe a sigh of relief. After waiting for such a long time, now the new function will be able to return a whole array instead of returning just one single result.

Instead of writing an Array Formula that was known as “MULTIPLE VLOOKUP” by everyone, we can list all the records of a data with the FILTER function easily. *We will be talking about the details in its own article. (Here are the article links of Microsoft, you can take a look.)

 

With the release of the XLOOKUP function, old functions like VLOOKUP and HLOOKUP are not needed anymore. And also, as they get into more detail and design what they can do, it seems like we will not need these functions:
VLOOKUP > HLOOKUP > INDEX > MATCH > OFFSET > IFERROR(VLOOKUP).. what else do you want?!👏🏻

 

Just one formula can:

  • Look up both horizontally and vertically,
  • Bring the first or last record, find the data considering the wildcard characters,
  • Bring the data we want on the left without a condition like the lookup_value has to be in the first column of the table_array in the VLOOKUP function,
  • Bring approximate vales in  certain range like the TRUE option in the range_lookup argument,
  • Allow you to say write this if no data is found without the need to use the IFERROR formula,
  • Find the most approximate lowest or highest value if there is no exact match.
  • And it offers all these in a way more faster way than before.

Enough with all the details, let’s see what this function does. 👍🏻

 

THE XLOOKUP FUNCTION SYNTAX

 

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

There are 6 arguments in this function.
The first 3 are required, the last 3 are optional.

Now let’s take a look at what these arguments mean, i.e. what the function wants from us and what we will give it.

Argument

 Description
lookup_value

Required

 The lookup value
lookup_array

Required

 The array or range to search
return_array

Required

 The array or range to return
[if_not_found]

Optional

 Where a valid match is not found, return the [if_not_found] text you supply.

If a valid match is not found, and [if_not_found] is missing, #N/A will be returned.
[match_mode]

Optional

 Specify the match type:

0 – Exact match. If none found, return #N/A. This is the default.

-1 – Exact match. If none found, return the next smaller item.

1 – Exact match. If none found, return the next larger item.

2 – A wildcard match where *, ?, and ~ have special meaning.
[search_mode]

Optional

 Specify the search mode to use:

1 – Perform a search starting at the first item. This is the default.

-1 – Perform a reverse search starting at the last item.

2 – Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.

-2 – Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.

 

In the few examples below, you can see what you need to write when there is the first record and last record is not found of the looked up data and how to use it when looking up with wildcard characters.

 

 

Let’s have an example of the Match Mode argument with exact match or highest and lowest options.

 

So far we got the data from the column we wanted by looking up in the column just like in VLOOKUP. Now, let’s use this function like HLOOKUP.

 

 

Now, let’s have a 2 dimension look up. Let’s match based on both the Product name and Month, and find the data in the intersecting cell. Users who know how to use the INDEX and MATCH functions usually do the matching we are about to the with the INDEX + MATCH + MATCH formulas. Now, let’s see how it is done with XLOOKUP.

 

 

Lastly, let’s compare the VLOOKUP and XLOOKUP function while finding the same data. At first, you will see that you get the same result, but when we add or delete a column, the VLOOKUP function will give us an incorrect result. But the result will not change in the XLOOKUP function. The reason is this: Since we write the column number that we want to get manually in VLOOKUP, the col_index_num stays stable but the column of the data changes and it returns an incorrect result. And in XLOOKUP, it doesn’t matter how many columns you add or delete, since we stated the column we want, that column changes dynamically and returns the correct result without producing any errors.

 

You can see the result in this example.

I hope this was helpful..  👍🏻
You can share this post and help many others get informed as well. Keep in mind that information gets more valuable when it is shared.

GEOGRAPHY LINKED DATA TYPE

GEOGRAPHY DATA TYPE

There were always data types in Excel. But they were traditional data types like text and number. Stocks and Geography data types are unique because they are accepted as linked data types. Linked data types have a connection to an online data source. For now, linked data types are only Stocks and Geography. Microsoft plans to share more linked data types in the future with the feedback from the clients. We will be sharing the new linked data types here.

Office 365 users can access a lot of data of countries by clicking their names only. You will see the Geography linked data type in the data Types group of the Data menu. This features allows us to get and list a lot of indicators like flags, capitals, currencies, data ratios, populations, military power, president, average life time of the countries through http://data.worldbank.org/. It is possible to access more detailed information of countries with the link I gave you, and you can even download this data in the Excel, Csv and Xml format and use them in your reports.

 

 

It is enough to write the country name in the A column one under another and choose the Geography type.

 

DATA CART

First, we wrote the names of 5 countries and chose the Geography data type.
After the country name in the cell is turned into the Geography data type, an icon is seen in the cell.
Click the icon to view the cart. Cart shows a list of areas and related values.
There might be a lot of areas that you can view and work with depending on the data. If you click the icon next to the country name, you will see that you can get the flag of that country and a lot more information.

 

 

When you select a country in a cell, an option list called Add Data pops up next to it.
When you choose the information you want to get from that list, that information will be listed quickly next to it.

 

 

You can list a lot of information about the country or countries you select next to one other and turn them into a table like below. If you want, you can indicate the data you want to get for a country and then drag it down to list the data of all countries in the indicated area.

 

 

You can write =A2. to call the data in the related areas from the defined names with a formula.

 

 

When you right-click the countries, you can see the Show Cart option within Data Type in the menu. (Ctrl + Shift + F5)  You can turn off the feature by converting the data into Text. You can cancel the Geography data type whenever you want like converting the tables into a range.

 

I hope this helps you.👍🏻
You can share this article with your friends and make it reach out to many people. Don’t forget: The more information is shared, the more valuable it gets.

HAVE YOU EVER SORTED WITH A FORMULA?

THE SORT FUNCTION

 

Hello everyone,

No doubt that one of the most frequently used functions in Excel is the Sort function.
This feature has 2 constants: A-Z and Z-A i.e. Ascending and Descending Sort.
We sort our data by certain field in pretty much all tables.
Have you ever tried to get this sorting action done with a formula?

 

WHAT DOES IT DO

You can easily sort columns or rows by the sort order you want in a different field from your mail table with the SORT function that is one of the dynamic array functions that are recently released after Office 365 . 

 

SYNTAX

=SORT(array,[sort_index],[sort_order],[by_col])

There are 4 arguments in the function.
The first one is required, the next 3 arguments are optional.

Now, let’s take a look at what these arguments mean, i.e. what the function wants from us and what we will give it.

Argument Description
array

Required

 The range, or array to sort
[sort_index]

Optional

 A number indicating the row or column to sort by
[sort_order]

Optional

 A number indicating the desired sort order; 1 for ascending order (default), -1 for descending order
[by_col]

Optional

A logical value indicating the desired sort direction; FALSE to sort by row (default), TRUE to sort by column

 

USE OF THE FUNCTION

  • Choose the fields you want from your table and state by which column and order you will sort.
  • When you choose a field for the array argument only, the data is sort based on the Ascending order.
  • If multiple columns are selected in the array argument, the column by which the sorting will be done is chosen.
  • You can choose the Descending order in the sort_order argument if you want. If you don’ choose it, the sort is always Ascending.
  • In the by_col argument, you can sort by column i.e. from left to right. If not chosen, the sorting will always be from top to bottom.

 

Let’s have an example and see what happens when we choose the array argument only.

 

Now, let’s choose multiple columns and state the sort_index argument as well.

 

Since we didn’t use the sort_order argument in the formula below, prices are sorted from the lower to the higher. Now, let’s use the sort_order argument in the same function and see our data sorted from the higher to the lower.

 

Lastly, let’s sort the data from left to right and now from top to bottom.

We will be talking about the other New Dynamic Array functions in our next articles.
And then we will be able to get things done way easier by using these new functions together. LONG LIVE THE NEW DYNAMIC ARRAY FUNCTIONS!

See you in other articles, bye. 🙋🏻‍♂️
You can share this article and help a lot of people get informed as well. 👍🏻

EXCEL’S HISTORY (EXCEL FROM PAST TO TODAY)

EXCEL’S HISTORY (Excel from Past to Today)

 

Hello everyone,

I received a lot of questions like “Who found Excel, how was it created? It develops all the time, I wonder how the first version was…” in many of the trainings I gave in corporate companies.

As an answer to there questions, I have prepared the history of Excel and wanted to take the old users to a time travel that would make them say: “”Oh! What times…” And it will serve as an informative article for the new users.

Are you ready for the time travel? 👨🏻‍🚀

 

We met the first Excel as VisiCalc in 1978.
In 1978, Harvard Business School students Dan Bricklin Father of the modern Excel  had to take on an analysis task for a case study. He had two alternatives to do that:

  1. He would do it manually.
  2. He would use a simple and unskillful computer program.

 

He thought that he didn’t have a better alternative and chose VisiCalc in the 1978 fall.
He programmed the first study and prepared the prototype of the concept he called VisiCalc. The first version of VisiCalc was a matrix of 4 columns and 20 rows.
It was not close to the electronic tables of our day but VisiCalc was still an accomplishment for that moment.

That was the first electronic table program and was published for Apple II at first.
It had skills like basic arithmetic operations, recalculating instantly and scroll bars.

 

1978 – VISICALC

 

 

1980 – SUPERCALC

 

 

1982 – MULTIPLAN

 

 

1983 – LOTUS 1-2-3

 

 

1985 – MICROSOFT EXCEL 1.0 (MAC) 

 

 

1987 – MICROSOFT EXCEL 2.0 (WINDOWS)

 

 

1990 – MICROSOFT EXCEL 3.0

 

1992 – MICROSOFT EXCEL 4.0

 

1993 – MICROSOFT EXCEL 5.0

 

 

1995 – MICROSOFT EXCEL 7.0 (Excel 95)

 

 

1997 – MICROSOFT EXCEL 8.0 (Excel 97)

1999 – MICROSOFT EXCEL 9.0 (Excel 2000)
2001 – MICROSOFT EXCEL 10.0
2003 – MICROSOFT EXCEL 11.0 (Excel 2003)

 

 

2007 – MICROSOFT EXCEL 12.0 (Excel 2007) 

 

 

2010 – MICROSOFT EXCEL 14.0 (Excel 2010)

2013 – MICROSOFT EXCEL 15.0 (Excel 2013)
2016 – MICROSOFT EXCEL 16.0 (Excel 2016)
2019 – MICROSOFT EXCEL 16.0 (Excel 2019) 

 

 

2019 – MICROSOFT EXCEL 16.0 (Office 365)

 

Now I leave you alone with the 1992 Microsoft Excel introduction..

 

Well, I hope that you enjoyed this few-minute trip to the past.
You can share this post and take a few more people to the past as well. 👍🏻

Good bye.

GET RID OF DUPLICATE RECORDS WITH THE UNIQUE FUNCTION

THE UNIQUE FUNCTION

 

Hello everybody,

In this article I will be talking about one of the The Dynamic Array Functions -the UNIQUE function. Before this function, we had to write a long nested formula and even an array formula in order to list the unique data in one or multiple columns. But now we can list the unique data in the field you want with a single and very simple function.

 

WHAT DOES IT DO

The UNIQUE function allows you to create from the data in the stated field. Use this function to return a list of unique values in a list or range.  When you press Enter, the results can be displayed in a contiguous range with a suitable size.

 

SYNTAX

=UNIQUE(array; [by_col]; [exactly_once])

There are 3 arguments in the function.
The first one is required, the other two are optional.

Now let’s take a look at what these arguments mean, i.e. what the function wants from us and what we will give it.

Argument Description
array

Required

 The range or array from which to return unique rows or columns
[by_col]

Optional

 The by_col argument is a logical value indicating how to compare.

TRUE will compare columns against each other and return the unique columns

FALSE (or omitted) will compare rows against each other and return the unique rows
[exactly_once]

Optional

 The exactly_once argument is a logical value that will return rows or columns that occur exactly once in the range or array. This is the database concept of unique.

TRUE will return all distinct rows or columns that occur exactly once from the range or array

FALSE (or omitted) will return all distinct rows or columns from the range or array

USE OF THE FUNCTION

We use this function to list the unique data in one column or multiple columns. For example, if we don’t know a formula to list the data in a row; we had to copy that column to a blank column and choose Remove Duplicates or we could list the Unique records only with the Advanced Filter. After writing this function, it will be enough to state the range from which we want the unique data.

You will see how easy it is one you watch the GIF below.

We have listed the unique data in the range we have stated by entering the first argument. Now let’s see what kind of results it returns with the other arguments.

The second arguments that is optional is the [by_col] argument.
TRUE and FALSE options are offered to us in this argument.
TRUE: Returns the unique columns.
FALSE: Returns the unique rows.

You can choose the FALSE option for the by_col argument if you want to get the unique data in any field.

UNIQUE / DUPLICATE DATA

The last argument [exactly_once] offers use two options concerning the data to be returned.
TRUE: Returns the data that is displayed only once. (Lists the ones that have been written only once, doesn’t take the ones written multiple times into consideration). FALSE: Returns all the different elements (Lists the unique ones in the range we choose).

We can choose the TRUE option if we want the data to be listed to be unique data only, and the FALSE option if we want the different data only.

Here is an example for that.

 

LONG LIVE THE NEW DYNAMIC ARRAY FUNCTIONS!

 

See you in other articles, bye. 🙋🏻‍♂️

You can share this post and help a lot of people get informed as well.👍🏻