GUID Function

What is the GUID Function?

A random id in all screens is created with the GUID function every moment. ID values are used as a key by the database systems like Common Data Service and SQL Server.

When the function is used on its own, it can include numbers, lower and upper case and hyphen. As you can see, this function returns pretty long outcomes but this function can be managed with some functions.

guid

GUID returns a different value each time the function is calculated.  If nothing else changes in the formula, it will have the same value throughout the execution of your app.

GUID is a volatile function when used without an argument. You can write it into a label in order to view or change the outcome of the function. You can convey it to the argument for the outcome to change actively.

A random id can be created while changing the page, opening the app, and saving data or with the timer.

 

GUIDE Function and Examples

Collect

For example, you can convey this function to a certain column by creating a collection.

Collect(Table1; {   Guid_Columns: GUID()  } )

Mid

GUID function creates an outcome that includes numbers, lower and uppercase and hyphen. For example, if you want to produce a 5-character outcome, you can use the Mid function.

When you add a label to the screen an set its Text property as Mid(GUID(); 1 ;5), a 5 character GUID is created.

Set

You can use the SET argument when you need to create a new argument id all the time.

Set the OnSelect property of a button you’ll add to the screen as Set(Guid_create ; Mid(GUID(); 1 ;5)) and the Text property of the label you’ve just added as Guid_create. Now each time you click the button, a new value will be created and it will be see in the  label.

 

Click here for the general usage of the GUID() function.

You can access the other Power Apps articles here.

Authorization with Department Control

You can enable interactive flow or do visual actions by creating rules in the application screen. Depending on the situation in the application scenario, you can connect objects or actions to department, user mail, location, title, marital status or user choice in multiple-choice actions.

Usually hide, show, freeze, open to edit or change color, location, size actions are done for the determined objects. You can apply all these controls in all application scenarios easily and flexibly.

Users whose department is HR will see the HR button that enables them to open the management and confirmation screen of HR.

It is better to do these actions step by step and in pieces while doing these controls. Like I mentioned in my previous article, the formula evaluations should be controlled in different labels and should be named depending on the outcome.

We will test the user department first in the example below.

Department Detection for the Existing User

We will use the Office365Users.MyProfile() formula that lets us get the existing login info of the user that opens the app for this action.

We will write the formula below in a label we named lbl_user_departmant and get the department information.

Office365Users.MyProfile().Department 

Is The Detected Department HR?

We will write the formula below into a label we named lbl_is_user_department_hr and check if the existing user is in the Human Resources department. If the user is in the HR, we will get the outcome as 1, if not; 0.

 

If the existing user is HR, what actions will be on the screen?

The Visible option of HR button will change depending on the 1/0 values that will come from the label named lbl_is_user_department_hr.

This way, the button will be showed and hidden depending on the department.

If(lbl_is_user_department_hr.Text=Value(1);true;false) 

Control Formulas

The labels named lbl_user_departmant and lbl_is_user_department_hr should be moved to the formula screen so that it is easier to revise them later. Click here to take a look at the article about creating a formula screen and its importance.

You can read about the details of the functions that are used to get user information through Office 365 here.

Similar Examples

You can do similar control with the same steps while filling a form prepared for personnel that consists of personal information like Name, Surname, ID Number, Gender, Date of Birth, Marital Status, Military Service Status.

When the gender is marked as Female, the military service status will be hidden (Visible) since it won’t be filled or it can be frozen (DisplayMode).

When the marital status is marked as single, the fields concerning spouse and children can be hidden (Visible) since they won’t be filled or they can be frozen (DisplayMode).

You can click here for other Power Apps articles.

FIELDVALUE Function

In this article, we will be informing you about the FIELDVALUE Function used with the Linked Data Types. You can use the FIELDVALUE function to retrieve field data from linked data types like the Stocks or Geography data types.

bağlantılı veri türleri

There are easier methods for writing formulas that reference data types, so the FIELDVALUE function should be used mainly for creating conditional calculations based on linked data types.

 

SYNTAX

fieldvalue fonksiyonu söz dizimi

This function requires two arguments.
It is necessary to enter both of these arguments.

There are these independent variables in the syntax of the FIELDVALUE function:

  • value– Function returns all matching fields(s) from the linked data type specified in the value argument.
  • field_name– The name or names of the fields you would like to extract from the linked data type.

Description

  • The FIELDVALUE function returns all matching fields(s) from the linked data type specified in the value argument.

  • The FIELDVALUE function belongs to the Lookup & Reference family of functions.

 

Examples

In the following basic example, the formula =FIELDVALUE(A2,”Area”) extracts the Area field from the geography data type for Turkey.

fieldvalue fonksiyonu örnek

Let’s list the data in the related field depending on the dynamic field choice about cities.

fieldvalue fonksiyonu örnek

 

Remarks

If you try to retrieve data from a non-existent data type field, the FIELDVALUE function will return the #FIELD! error. For instance, you might have entered “Field“, when the actual data type field is named “Area“. Double-check your formula to make sure you’re using a valid field name. If you want to display a list of field names for a record, select the cell for the record, and press Ctrl+Shift+F2 .

You can get more information on Microsoft Support.

See you in other articles, bye. 🙋🏻‍♂️
You can share this post with your friends and help them get informed as well.👍🏻

Sort and Fill Data with Custom Lists

In this article, we will be talking about how to create a custom list in Excel and what we can do with it. We can get faster in entering and analyzing data by creating data tables quickly with custom lists. If you already have a data list, i.e. certain product names, brands, stock names, region names etc.; if you add them to custom lists once, it is possible to list all the data in the list quickly instead of writing them over and over again. You just need to write any data from that list into a cell and drag it down. In addition, you can find articles concerning other features of Excel on our blog.

HOW ARE THE CUSTOM LISTS STORED?

Once you create a custom list, it is added to your computer registry, so that it is available for use in other workbooks. If you use a custom list when sorting data, it is also saved with the workbook, so that it can be used on other computers, including servers where your workbook might be published to Excel Services and you want to rely on the custom list for a sort.

However, if you open the workbook on another computer or server, you do not see the custom list that is stored in the workbook file in the Custom Lists popup window that is available from Excel Options, only from the Order column of the Sort dialog box. The custom list that is stored in the workbook file is also not immediately available for the Fill command.

If you prefer, add the custom list that is stored in the workbook file to the registry of the other computer or server and make it available from the Custom Lists popup window in Excel Options. From the Sort popup window, in the Order column, select Custom Lists to display the Custom Lists popup window, then select the custom list, and then click Add.

 

HOW ARE CUSTOM LISTS CREATED?

You need to access the existing custom lists box to create a custom list. There are two ways to access that box.

  1. File > Options > Advanced >  Edit Custom Lists

özel listeleri düzenle

 

2. Data > Sort > Order > Custom Lists

özel listelere ulaşmak

 

BUILT-IN LISTS

Excel provides day-of-the-week and month-of-the year built-in lists. The names will change depending on the language you use.

Built-in lists
Sun, Mon, Tue, Wed, Thu, Fri, Sat
Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday

Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec

January, February, March, April, May, June, July, August, September, October, November, December

Note: You cannot edit or delete a built-in list.

 

For this reason, when we type January into a list and drag it down, a list that goes like February, March, April is created. If these lists didn’t exist, when we dragged the list it would have gone like January, January, January.

yerleşik özel listeler

 

HOW DO YOU CREATE A CUSTOM LIST MANUALLY?

  1. Access the Custom Lists Box with one of they ways we’ve mentioned above.
  2. Enter the data you want into the List entries field.
  3. Click Add.
  4. You will see that it is added in the Custom Lists field.
  5. Finally, click the OK button.

özel liste penceresi

 

Now you will see that when you write and drag any data that you’ve entered to the List entries field, every single one of them will be listed.

manuel özel liste

 

Let’s quickly create a data table that contains of the data we created and also months.

 

özel listeler hızlı doldur

 

IMPORT YOUR DATA TO YOUR CUSTOM LISTS

If you have steady and unique data in a range of cells, you can import that date to the customs listens box as a whole and use them in your lists. For that you need to go to the Custom Lists box from File > Options > Advanced > Edit Custom Lists. Afterwards, we will select the cell range and import the content.

 

 

toplu listeyi custom listden içeri aktarmak

 

And the thing about accessing these lists from the Sort window is that you can use them with the Sort feature.

For example; there are multiple lists in a table and you want to sort the list but you want 2019 and 2020 be the primary ones. You can add the 2019 and 2020 data into the Lists box and sort the way below.

 

custom list sıralama yapmak

 

You can get detailed information on Office Support.

See you in other articles, bye. 🙋🏻‍♂️
You can share this post with your friends and make sure that they are informed too.👍🏻

Create Custom Rules in Conditional Formatting

I will be talking a bit about how you can create custom rules for conditional formatting. Of course, to be able to create these rules, you need to be able to write formulas, at least a bit. I will tell you about how to create simple formulas and use them in conditional formatting. As you get better at writing formulas, I am sure that you will create better rules. You can take a look at our blog to find other articles about the other features.

 

WHAT IS CONDITIONAL FORMATTING?

 koşullu biçimlendirme

One of the most frequently used features in Excel is Conditional Formatting. This feature is usually used to color and highlight backgrounds of certain cells that comply with a specific rule. It is found within Styles in the Home menu. There are some available rules you can use. When the available rules are not enough for you, you can create your own rules and format the cell in compliance with those rules. Of course, for this you need to know how to write formulas like I have mentioned above.

We can see the available prepared rules when we choose Conditional Formatting like you can see below. We can easily format by color, text, and date. If the available rules are not enough for you, then you can choose “Use a formula to determine which cells to format” from the New Rule option and highlight the cells that comply with your rules by writing to the formula into the related field. If we want, we can create more than one Conditional Formatting in a field. We can use the Manage Rules option to see and change the conditions of these formats.

You can use the Clear Rules option to clear rules in a part of the page or in the whole page.

Let’s dig a bit deeper with practical examples.

APPLY CUSTOM RULES IN CONDITIONAL FORMATTING

HIGHLIGHT TEXTS

In our first example, we will make a simple application about creating custom rules in conditional formatting. Let’s detect and highlight backgrounds of texts in the field we determine. For this, we need a function that would detect if a value in a cell is text or not. This function is called: ISTEXT. We will be able to automatically highlight the data if they are text in the specified field.

You can see how to do it in this GIF and do the same thing for your work.

emetinse ile koşullu biçimlendirmede metin olanları renklendirmek

 

HIGHLIGHT WEEKENDS

Let’s detect the weekends of the dates in a columns and highlight their backgrounds for our second example. We will need a function for this as well, and it is called WEEKDAY.  With this function, we can find out which day a date is in its week. I.e, it will give us a number between 1 and 7; and we will highlight the dates that happen to be weekends by writing a rule of if the number is bigger than 5.

First, I want to do a live application of this formula for you to do it live as well and comprehend it better. After writing the formula to the cell, we will copy that formula and paste it to the use formula section in conditional formatting.

 

koşullu biçimlendirmede hafta sonunu renkli göstermek

 

And as the last step, we will write this formula to the related field, click on format and complete the action.

 

koşullu biçimi uygulama

 

If you say that you need more information, you can check Office Support.

 

See you in other articles, bye. 🙋🏻‍♂️
You can share this post with your friends and enable them to get informed as well.👍🏻

How to Make a Histogram in Excel

In this article, we will be talking about how to make a histogram in Excel. It looks a lot like the other bar charts, but separates your numbers into ranges the way you determine. Compared to the other chart types, histograms facilitate determining categories and frequencies of occurrence alongside different data. Don’t forget that you can take a look at our blog to see our articles about other topics. Let’s start. 👍🏻

 

HISTOGRAM IN EXCEL

You can find the Histogram Chart within built-in charts in Office 2016 and later version. Also, you can create a histogram in Excel with the Histogram tool. You create it by entering the numbers that represent the frequency range you want to use and the data you want to analyze. Histograms are supported by Office 365, Excel 2019, 2016, 2013, 2010, 2007 and Excel for Mac, but the steps you take depend on the Excel version you use.

Reminder: You need the Analysis ToolPak extension to use the Histogram tool in the previous versions of Excel 2016. This extension is not supported in Excel Online. But, you can display a histogram created in a desktop version of Excel by using Excel Online.

 

How Do You Create a Histogram Chart in Excel?

Since it is one one the built-in charts in Excel 2016 and later versions, it is pretty easy to use.

  1. To start, enter the data you want to use in your histogram into a worksheet. For example, enter the names of your employees in your company with 1000 workers into a column, and their ages into another column.
  2. Select any cell on your data table.
  3. Go to the Insert menu.
  4. Choose Insert Statistic Chart in the charts group.
  5.  Choose Histogram chart type.
  6. Double-click on the horizontal axis.
  7. Arrange the bin width as 5.
  8. Add Data labels.
  9. Your chart is ready.

Creating a Histogram chart is as simple as that. If we want to apply the steps above, a Histogram chart like this will come up. (The colors have been edited.)

Excel'de histogram grafik nasıl oluşturulur?

 

Let me show you how the steps are done with a GIF:

excel'de histogram oluştur

 

Creating a Histogram in Excel 2013, 2010 or 2007

Like I mentioned above, you need to active the Analysis ToolPak extension to be able to create a histogram chart in versions prior to 2016.

  1. Click the File menu.
  2. Choose Options.
  3. Click on Extensions.
  4. Push the Go button.
  5. Choose the Analysis ToolPak extension.
  6. Click on the OK button.

 

After following these instructions, you will see the Analysis ToolPak extension within the Analysis group on the right of the Data menu. We will show you how to activate it on the GIF below.

Excel'de histogram eklentisi aktif etmek

 

To get a Histogram from the Analysis tool, let’s write the bin width into a field first. For example; let’s write the bins starting from D2 as 28, 33, 38, 43, 48 to find how many people there are between the ages of 23 and 48 with 5 year gaps in a company with 1000 employees. You can check the GIF below for the next step.

 

histogram veri çözümleme

 

HINT: We can obtain the same Histogram that comes with data analyzes with the FREQUENCY function. We have data, and certain tracking ranges. With the FREQUENCY function, we can easily find the frequency of repetition of all values within the specified range.

sıklık fonksiyonu ile histogram

 

You can find chart and application samples about everything I’ve mentioned in this Excel file.  👉🏻 excel icon

 

 

You can get more information on Microsoft Support.

 

See your in another article, good bye.🙋🏻‍♂️
You can share this post with your friends and help them get informed as well.👍🏻

Rename a Worksheet

In this article, we will be informing you about how to rename a worksheet. Two changes that help to edit and define worksheets and the data they contain is to rename the worksheet and to change the color of the sheet tab under the work area. You will see the instruction and actions concerning this. You can take a look at our blog to find our articles about the other features.

 

3 ways to rename a worksheet

  1. Double-click the sheet tab and type the new name.

  2. Right-click the sheet tab, click Rename, and type the new name.

  3. Use the keyboard shortcut Alt+H > O > R, and type the new name.

 

1- Double-click the Sheet Tab

excel sayfa adını yeniden adlandırma

This method works in all versions of Windows, Mac, 365 and online Excel.

  1. Double-click on the name of the sheet tab to emphasize the name in the tab.
  2. Type a new name for the worksheet.
  3. Press Enter to complete renaming the worksheet.
  4. The new name should be displayed on the sheet tab.

 

sağ tık menüsünden sayfa adını değiştirme2- Right-click the Sheet Tab

This method works in all versions of Windows, Mac, 365 and online Excel.

  1. Right-click the sheet tab of the worksheet you want to rename to open the context menu.
  2. Click Rename on the menu list to emphasize the current sheet name.
  3. Type a new name for the worksheet.
  4. Press Enter to complete renaming the worksheet.
  5. The new name should be displayed on the sheet tab.

 

3- Access the Ribbon Options with your mouse

çalışma sayfalarını adlandırma

This method works in all versions of Windows, Mac, 365 and online Excel.

  1. Click the sheet tab of the worksheet to be renamed to make it the active page.
  2. Click the Home tab of the ribbon.
  3. Click Format on the Cells to open the drop-down menu.
  4. Click Rename Sheet to emphasize the sheet tab on bottom of the screen.
  5. Type a new name for the worksheet.
  6. Press Enter to complete renaming the worksheet.

 

Things to pay attention to while naming a worksheet:

  • Page name cannot be blank.
  • It cannot consist of more than 31 characters.
  • It cannot contain any of these characters: / ? * : [ ] For example, 05/04/2020 cannot be a valid worksheet name but 05-04-2020 can be.
  • It cannot start or end with an apostrophe (‘), but it can be used between texts and numbers in the name.
  • It cannot be named “History“. This is a special dictionary used for special purposes within Excel.

 

You can click here to get more information.

See you in other posts, bye. 🙋🏻‍♂️
You can share this post with your friends and enable them to get informed as well. 👍🏻

Get a Picture from a Cell with a Formula

In this article, I want to explain you how to get a picture to a cell with a formula. As you know, we can get the data of the columns we want from a table by looking up for a value on the specified table with lookup and reference functions like VLOOKUP but we can only get the data inside a cell. At this point, what we want to tell you is that if there is a picture in the are we want to get, we can get it with a formula. By the way, we would like you to read and analyze our articles about Office, you can find them on our blog.

 

Get a Picture to the Cell with a Formula

Let’s say that we have two different pages. On one page, there are products, brand names etc. and on the other page there are product and brand pictures. We can get the picture on the second page next to the product and brand name on the first page with a formula that we’ll write. While doing that, as you’ll see below, we need to define names. We cannot get the picture in a cell without defining names.

 

Here is how you do it step by step:

  • Create a Data Verification list with brands.
  • Write the main formula necessary for this action.
  • Name the formula we created with the Name Manager.
  • Complete the process by assigning the name to the picture.

 

Let’s start understanding how to do it with pictures! 😀

We talked about creating a data verification list first, let’s take a look at it.

veri doğrulama listesine almak

 

We took the brand names to the data verification list.Let’s write the formulas that will do the job. I am leaving the formula here, you can see how I write it in the gif below. 

=İNDİS('6-1'!$B$2:$B$6;KAÇINCI('6'!$A$2;'6-1'!$A$2:$A$6;0))

 

formülle resim getirme formülü

 

Now we’ll name the formula I have written, assign the picture on the first page and complete this action.

 

formüle ad verme

 

As you can see, we have learned how to get a picture to a cell with a few steps.. We have called a picture on another page to the active page with brand names. You can execute this kind of examples by getting pictures dynamically.

Also, you can get more information about defining names on Microsoft Support.

 

See you in other articles, bye.🙋🏻‍♂️
You can share this post with your friends and help them get informed. 👍🏻