Collaborate on Billing Statement in Excel for HR with Ease Using airSlate SignNow

Billing statement in excel for HR

top 10 excel interviews questions how to pass any excel assessment test one conditional formatting conditional formatting can be used to highlight cells based on your rules [Music] you can find it in the home tab inside the style section [Music] here we want to highlight the above 60 marks with green and below 60 with red [Music] for another example we want to highlight profits above the average [Music] if we want to remove the formatting we can go to the conditional formatting and select clear rules 2. absolute reference versus relative cell reference here we want to calculate the difference between each mark and the average [Music] if we use relative cell references the cell reference for marks and averages will change when we apply the formula to other cells [Music] but our average value is constant so we should use the absolute cell reference by adding a dollar sign in the middle of the cell address [Music] three spell checking spell checking your excel data is so simple go to the review tab and select spelling it will show you the mistakes and suggest some words as a replacement then you should just select the correct word and click on change [Music] 4 removing duplicated data removing duplicated data is an important part of data analysis [Music] in order to do it go to the data tab data tool section and click on remove duplicate [Music] here we had four duplicate values and they got removed 5. putting a border around our table [Music] first select your table then add any kind of border that you like from the home tab and fonts section [Music] you can also highlight some cells using the highlight tool [Music] 6 most frequently used charts in an interview they may ask you to name some excel charts and show how you use them [Music] one simple and quite useful chart is the line chart you can find it in recommended charts in the insert tab the line chart can show multiple data sets in one chart another useful chart is the column chart the third one is the pie chart which can only show one data set for example sales or profit over years you can find the pie chart in the all charts tab [Music] seven different views and their use sometimes you want to print your excel sheet or save it as a pdf file to see how your document will look and edit it you can use the options in the view tab the page break preview will show you the page breaks positions you can also move page breaks in this view [Music] the page layout view will show you how your printed document will look you can edit the header and footer in the page layout view [Music] eight a function and how to use it imagine we have some students and we want to write in a column that they have passed if their score is above 60 or failed if it is below 60 just remember to write the text inside quotations [Music] 9 vlookup function and how to use it vlookup function might look complicated but it is very simple the vertical lookup will search for a value and return any value from the same row in the function variables the first variable is the value you are looking for and the second variable is your table address where you want to search the third variable is the number of the column for our function returned value for example here it is the second column so we use two and the function last variable is true or false true for an approximate match and false for an exact match [Music] 10 pivot tables and how to use them pivot tables are another useful option for data analysis in this example we have several cars from different brands and we want to analyze our data we can add a pivot table from the insert tab and tables section first we have to select our chart and it should contain column or row name tags [Music] we can put specifications in columns or rows of our pivot table [Music] or change values settings in values settings we can use sum count average or anything else [Music] also we can select how values are shown for example we can show values as a percentage of grand total [Music] thank you for watching please like and subscribe [Music]