Thứ Bảy, 25 tháng 6, 2016

Microsoft Excel 2017

Microsoft excel 2017 is the spreadsheet editor included in the popular Microsoft Office suite that will offer you all the essential features when it comes to creating this kind of documents. You'll be able to analyze, manage and share the information to take more precise decisions, with a clear idea of the trends of the most important data for your company

When it comes to spreadsheets, take no chances

It's not every day that you can download a free spreadsheet and be totally satisfiedMicrosoft Excel offers total guarantees. Microsoft has spent many years committed to the development of this type of tool, something that translates into an application that works as well as it possibly could.

Features

  • Create customized spreadsheets.
  • Insert all kinds of statistical tables and graphics to represent the data.
  • Import data for the document from an SQL Database or other programs.
  • Create calculation rules and customized macros.
  • Compare data, detect patterns and trends.
  • Recover spreadsheets you had closed accidentally.
  • Allows you to work collaboratively.
  • Publish the data online and make them accessible wherever you are, where you'll also be able to edit them or access them from your mobile.
  • New 'Mini chart" feature to insert small charts into each cell.

Renovated interface to optimize your work

The interface of Microsoft Excel, in accordance with the rest of products of the suite to which it belongs, it includes the 'Ribbon' interface, thus managing to make using the tools easier to use and more accessible. The elements are well distributed, making it more dynamic, which is also encouraged by the new 'Backstage' menu or the integrated visualization of related data and graphics. As well as this, it's worth mentioning the control over styles and icons and the new fillers, that are ideal to provide each value with more visibility.
There are several templates that can be used as a starting point to work, even though you'll have the possibility to customize your own.

Thứ Tư, 22 tháng 6, 2016

Free MS Excel Alternative Software

Are there any free  excel  programs with equivalent features to replace the expensive Microsoft Excel? There are many people everyday who ask this question over the internet, while, the answer is: YES! 
If you are looking for free excel replacement featured with the following functions: 
Formulas and functions 
Auto fill data  , Sorting data  , Data Filter , Pivot Table , Chart Text , Hyperlink,  Freeze Rows and Columns ... , then you will find this free Excel alternative really useful.

Figure 1 Kingsoft Spreadsheets interface
Highly compatible with all Microsoft Excel files. Open *.xls and *.xlsx format files made by Microsoft Excel; create and save spreadsheets in *.xls, *.xlsx.
Figure 2 Open and save Excel format workbooks
Convert Spreadsheets workbooks into PDF files - Export spreadsheet to PDF files directly within the program. Multiple Tabs interface makes it more convenient to switch between several workbooks. It allows you to open many workbooks page on a single window task to optimize your window sequences. 
A great number of formulas are provided for both daily office work and professional business such as Sum, Average, V lookup, Mid, TRIM, TEST, etc. 
Shortcut functions to quickly handle data - These functions include an auto filer, sort ascending, sort descending, auto sum, and more. You can easily edit data by using them.

Thứ Bảy, 18 tháng 6, 2016

Count cells that contain text

excel count formula
If you need to count the number of cells that contain text (i.e. not numbers, not errors, not blank), you can do so with the COUNTIF function and a wildcard. In the generic form of the formula (above), rng is a range of cells, and "*" is a wildcard matching any number of characters.
In the example, the active cell contains this formula:
=COUNTIF(B4:B8,"*")
Here's how the formula works:
COUNTIF counts the number of cells that match the supplied criteria. In this case, the criteria is supplied as the wildcard character "*" which matches any number of text characters.
A few notes:
  • The logical values TRUE and FALSE are not counted as text
  • Numbers are not counted by "*" unless they are entered as text
  • A blank cell that begins with an apostrophe (') will be counted.
You can also use SUMPRODUCT to count text values along with the function ISTEXT like so:
=SUMPRODUCT(--ISTEXT(rng))
The double hyphen (called a double unary) coerces the result of ISTEXT from a logical value of TRUE or FALSE, to 1's and 0's. SUMPRODUCT then sums these values together to get a result.
Web : excel

Thứ Tư, 15 tháng 6, 2016

EXCEL KEYBOARD SHORTCUTS


keyboard shortcuts listed in this topic refer to the U.S. keyboard layout. Keys on other keyboard layouts might not correspond to the keys on a U.S. keyboard. Keyboard shortcuts for laptop computers might also differ.
Some function key shortcuts conflict with default key assignments in Mac OS X version 10.3 or later. To use them, you must change the settings for your function keys. On the Apple menu, go to System Preferences. Under Hardware, click Keyboard >Keyboard and then check Use all F1, F2, etc. as standard function keys.
Function keys

Web : excel

Chủ Nhật, 12 tháng 6, 2016

EXCEL FORMULAS












This page provides examples of how to use excel formulas to perform many common tasks. Although many people use the term 'Excel Formulas' to refer to the Excel Built-In Functions, the term 'Excel Formula' can encompass a wider range of operations in Excel. Therefore on this site, when we use the term 'Excel Formulas' (or 'Excel Formulae'), we are generally referring to any combination of Excel Operators and/or Excel Functions. This may be as simple as a basic addition (eg. "=A1+B1"), or it could be a complex combination of Excel Operators and multiple nested Excel Functions.
Web : excel
Filter: 
FormulaRelated Functions
Count cells between datesDATECOUNTIFS
Count cells between two numbersCOUNTIFCOUNTIFS
Count cells equal to case sensitiveSUMPRODUCTEXACT
Count cells equal to either x or yCOUNTIF
Count cells equal to one of many thingsSUMPRODUCTCOUNTIF
Count cells greater thanCOUNTIF
Count cells less thanCOUNTIF
Count cells not equal toCOUNTIF
Count cells not equal to x or ySUMPRODUCTCOUNTIFS
Count cells that are blankCOUNTA
Count cells that are not blankCOUNTA
Count cells that begin withCOUNTIF
Count cells that contain either x or yISNUMBERSUMPRODUCTCOUNTIFFIND
Count cells that contain errorsISERRISERRORSUMPRODUCT
Count cells that contain five charactersCOUNTIF
Count cells that contain negative numbersCOUNTIF
Count cells that contain numbersCOUNT
Count cells that contain odd numbersMODSUMPRODUCT
Count cells that contain positive numbersCOUNTIF
Count cells that contain specific textISNUMBERSUMPRODUCTCOUNTIFFIND
Count cells that contain textISTEXTSUMPRODUCTCOUNTIF
Count cells that do not containCOUNTIF
Count cells that do not contain errorsISERRORNOTSUMPRODUCT
Count cells that end withCOUNTIF
Count if row meets internal criteriaSUMPRODUCT
Count if row meets multiple internal criteriaSUMPRODUCT
Count if two criteria matchSUMPRODUCTCOUNTIFS
Count matches between two columnsSUMPRODUCT
Count sold and remainingCOUNTA
Count total matches in two rangesSUMPRODUCTCOUNTIF

Sum

Filter: 
FormulaRelated Functions
3D SUMIF for multiple worksheetsINDIRECTSUMIFSUMPRODUCT
Calculate running totalSUM
Subtotal by colorSUMIF
Subtotal invoices by ageSUMIF
Sum bottom n valuesINDIRECTROWSUMSUMPRODUCTSMALL
Sum by groupSUMIF
Sum by monthEOMONTH
Sum columns based on adjacent criteriaSUMPRODUCT
Sum entire columnSUM
Sum every nth columnCOLUMNMODSUMPRODUCT
Sum if begins withSUMIF
Sum if between
Sum if by yearDATE
Sum if cell contains text in another cellSUMIF
Sum if cells are equal toSUMIF
Sum if cells are not equal toSUMIF
Sum if cells contain an asteriskSUMIF
Sum if cells contain both x and y
Sum if cells contain either x or yISNUMBERSUMPRODUCTFINDSEARCH
Sum if cells contain specific textSUMIF
Sum if date is betweenDATE
Sum if date is greater thanDATESUMIF
Sum if ends withSUMIF
Sum if equal to either x or ySUMIFSUMPRODUCT
Sum if greater thanSUMIF
Sum if less thanSUMIF
Sum if multiple criteria
Sum if not blankSUMIF
Sum top n valuesINDIRECTROWSUMSUMPRODUCTLARGE
Sum visible rows in a filtered listSUBTOTAL

Average

FormulaRelated Functions
Average numbersAVERAGE
Average numbers ignore zeroAVERAGEIF
Average top 3 scoresAVERAGELARGE
Weighted averageSUMSUMPRODUCT

Min and Max

Filter: 
FormulaRelated Functions
Maximum if multiple criteriaIFMIN
Maximum value
Maximum value ifIF
Minimum if multiple criteriaIFMIN
Minimum valueMIN
Minimum value ifIFMIN
nth largest valueLARGE
nth largest value with criteriaLARGE
nth smallest valueSMALL
nth smallest value with criteriaSMALL

Conditional Formatting

Filter: 
FormulaRelated Functions
Highlight blank cellsISBLANKLEN
Highlight cells that begin withIFERRORCOUNTIFFIND
Highlight cells that containISNUMBERFINDSEARCH
Highlight cells that end withCOUNTIFEXACTLENRIGHT
Highlight cells that equalEXACT
Highlight column differencesNOTEXACT
Highlight dates betweenDATEAND
Highlight dates greater thanDATE
Highlight dates in same month and yearDATETEXT
Highlight dates in the next N daysTODAYAND
Highlight dates that are weekendsWEEKDAYOR
Highlight duplicate rowsSUMPRODUCTCOUNTIFCOUNTIFS
Highlight duplicate valuesCOUNTIF
Highlight entire rows
Highlight every other rowISEVENISODDROWMOD
Highlight integers onlyMOD
Highlight rows that containFINDSEARCH
Highlight rows with dates betweenDATEAND
Highlight unique valuesCOUNTIF
Highlight values betweenAND
Highlight values greater than
Highlight values not between X and YANDNOT
Highlight values that don't existCOUNTIF

Lookup

Filter: 
FormulaRelated Functions
Approximate match with multiple criteriaIFINDEXMATCH
Basic INDEX MATCH approximateINDEXMATCH
Basic INDEX MATCH exactINDEXMATCH
Calculate grades with VLOOKUPVLOOKUP
Calculate shipping cost with VLOOKUPVLOOKUP
Dynamic lookup table with INDIRECTINDIRECTVLOOKUP
Exact match lookup with INDEX and MATCHINDEXMATCHEXACT
Exact match lookup with SUMPRODUCTSUMPRODUCTEXACT
Extract multiple matches into separate columnsIFERRORCOLUMNSINDEXMATCHROWSMALL
Faster VLOOKUP with 2 VLOOKUPSNAVLOOKUP
Find missing valuesMATCHVLOOKUPCOUNTIF
Get address of lookup resultCELLINDEXMATCH
Get employee information with VLOOKUPVLOOKUP
Get first match cell containsISNUMBERINDEXMATCHSEARCH
Get first non-blank value in a listISBLANKINDEXMATCH
Get first partial match in a rangeINDEXMATCH
Get first text value in a listVLOOKUP
Get nth match with INDEX / MATCHINDEXMATCHSMALL
Get nth match with VLOOKUPVLOOKUPCOUNTIF
Group arbitrary text valuesVLOOKUP
Group numbers with VLOOKUPVLOOKUP
Lookup up cost for product or serviceVLOOKUP
Map inputs to arbitrary valuesCHOOSEVLOOKUP
Map text to numbersVLOOKUP
Match first errorISERRORMATCH
Max if criteria matchIF
Partial match against numbers with wildcardMATCHTEXT
Partial match with VLOOKUPVLOOKUP
Position of max value in listMATCH
Related info for max value in rangeINDEXMATCH

If

Filter: 
FormulaRelated Functions
If cell begins with x, y, or zSUMCOUNTIF
If cell containsIF
If cell equalsIF
If cell is blankISBLANKIF
If cell is greater thanIF
If cell is not blankISBLANKIFNOT
If cell is this OR thatIFOR
If cell is x or y and zANDIFOR
If elseIF
If NOT this or thatIFNOTOR
If this AND thatANDIF
Nested IF function exampleIF

Round

Filter: 
FormulaRelated Functions
Get decimal part of a numberTRUNC
Get integer part of a numberINTTRUNC
Round a numberROUND
Round a number downROUNDDOWN
Round a number down to nearest multipleFLOOR
Round a number to n significant digitsABSINTLOG10ROUND
Round a number to nearest multipleMROUND
Round a number upROUNDUP
Round a number up to nearest multipleCEILING
Round a number up to next halfCEILING
Round a price to end in .99ROUND
Round time to nearest 15 minutesMROUND
Round to nearest 1000ROUND
Round to nearest 5CEILINGFLOORMROUND

Date And Time

Filter: 
FormulaRelated Functions
Add business days to dateNETWORKDAYSWORKDAY
Add workdays to date custom weekendsNETWORKDAYS
Calculate elapsed work timeIFMOD
Calculate years between datesYEARFRACINT
Convert date string to date timeDATEVALUETIMEVALUELEFTMID
Convert date to Julian formatDATEYEARTEXT
Convert date to textTEXT
Convert hours to time
Convert minutes to time
Convert seconds to time
Convert text to dateDATELEFTMIDRIGHT
Convert time to time zoneMOD
Count day of week between datesWEEKDAYINDIRECTROWSUMPRODUCT
Custom weekday abbreviationWEEKDAYCHOOSE
Date is same monthMONTH
Date is same month and yearMONTHYEAR
Date is workdayWORKDAY
Days in monthDAYEOMONTH
Display the current dateTODAY
Display the current date and timeNOW
Extract date from a date and timeINTTRUNC
Extract time from a date and timeMOD
Get age from birthdayTODAYYEARFRACINT
Get day from dateDAY
Get day name from dateWEEKDAYCHOOSE
Get days before a dateTODAY
Get days between datesDAYSTODAY
Get days between dates ignoring yearsDATEDIF
Get first day of monthDAY
Get first day of previous monthEOMONTH

Date Series

FormulaRelated Functions
Series of dates by day
Series of dates by monthDATEDAYEOMONTHMONTHYEAR
Series of dates by weekendsWEEKDAYIF
Series of dates by workdaysWEEKDAYWORKDAYIF
Series of dates by yearDATEDAYMONTHYEAR

Text

Filter: 
FormulaRelated Functions
Add a line break with a formulaCHAR
Add line break based on OSINFOCHAR
Cell contains all of many thingsISNUMBERSUMPRODUCTCOUNTASEARCH
Cell contains any numbersCOUNTFIND
Cell contains one of many thingsISNUMBERSUMPRODUCTSEARCH
Cell contains some words but not othersANDCOUNTSEARCH
Cell contains specific textISNUMBERFINDSEARCH
Cell contains which thingsISNUMBERFINDSEARCH
Cell equals one of many thingsSUMPRODUCT
Clean and reformat telephone numbersSUBSTITUTE
Compare two stringsEXACT
Count line breaks in cellISBLANKCHARLENSUBSTITUTE
Count specific characters in a cellLENSUBSTITUTEUPPER
Count specific characters in a rangeSUMPRODUCTLENSUBSTITUTEUPPER
Count specific words in a cellLENSUBSTITUTEUPPER
Count specific words in a rangeSUMPRODUCTLENSUBSTITUTEUPPER
Count total characters in a cellLEN
Count total characters in a rangeSUMPRODUCTLEN
Count total words in a cellISBLANKLENSUBSTITUTETRIM
Count total words in a rangeSUMPRODUCTLENSUBSTITUTETRIM
Double quotes inside a formulaCHAR
Extract nth word from text stringLENMIDREPTSUBSTITUTETRIM
Get first wordISERRORFINDLEFT
Get last wordREPTRIGHTSUBSTITUTETRIM
Position of 2nd 3rd etc instance of characterFINDSUBSTITUTE
Remove last n characters from textLEFTLENVALUE
Remove leading and trailing spaces from textCLEANSUBSTITUTETRIM
Remove text by matchingSUBSTITUTE
Remove text by positionREPLACE
Remove text by variable positionFINDREPLACE

Finalcial

FormulaRelated Functions
Calculate compound interestFV
Calculate simple interest

Workbook

FormulaRelated Functions
Get full workbook name and pathCELL
Get sheet name onlyCELLFINDMID
Get workbook name and path without sheetCELLFINDLEFTSUBSTITUTE
Get workbook name onlyCELLFINDMID
Get workbook path onlyCELLFINDLEFT

Internet

FormulaRelated Functions
Get domain from email addressFINDLENRIGHT
Get domain name from URLFINDLEFT
Get name from email addressFINDLEFT
Get top level domain (TLD)FINDLENRIGHTSUBSTITUTE

Names

FormulaRelated Functions
Get first name from Last, FirstFINDLENRIGHT
Get first name from nameFINDLEFT
Get last name from Last, FirstLEFTLEN
Get last name from nameFINDLENRIGHTSUBSTITUTE
Get middle name from full nameLENMIDTRIM
Join first and last nameCONCATENATE
Put names into proper casePROPERTRIM

Percentage

Filter: 
FormulaRelated Functions
Calculate percent variance
Decrease by percentage
Get amount with percentage
Get original price from percentage discount
Get percent change
Get percentage discount
Get percentage of total
Get profit margin percentage
Get total from percentage
Increase by percentage
Percent of goal
Percent of students absent

Range

Filter: 
FormulaRelated Functions
Address of first cell in rangeCELLADDRESSCOLUMNROW
Address of last cell in rangeADDRESSCOLUMNCOLUMNSROWROWS
All cells in range are blankSUMPRODUCT
First column number in rangeROWSMIN
First row number in rangeROWMIN
Get relative column numbers in rangeCOLUMN
Get relative row numbers in rangeROW
Last column number in rangeCOLUMNCOLUMNSMIN
Last row number in rangeROWROWSMIN
Range contains a value not in another rangeISNAMATCHSUMPRODUCT
Range contains numbersISNUMBERSUMPRODUCT
Total cells in a rangeCOLUMNSROWS
Total columns in rangeCOLUMNS
Total rows in rangeROWS

Miscellaneous

Filter: 
FormulaRelated Functions
Build hyperlink with VLOOKUPHYPERLINKVLOOKUP
Calculate a ratio from two numbersGCD
Change negative numbers to positiveABS
Extract unique items from a listINDEXMATCHCOUNTIF
Flag first duplicate in a listCOUNTIF
Flip table rows to columnsTRANSPOSE
Get value of last non-empty cellLOOKUP
Increment a calculation with ROW or COLUMNCOLUMNROW
List contains duplicatesSUMPRODUCTCOUNTIF
Longest winning streakIFFREQUENCY
Mixed reference for multiplication table
Pad a number with zerosREPTTEXT
Random date between two datesWORKDAYRANDBETWEEN
Random number between two numbersRANDBETWEEN
Random text valuesCHOOSERANDBETWEEN
Randomly assign data to groupsCHOOSERANDBETWEEN
Range contains one of many substringsISNUMBERSUMPRODUCTSEARCH
Range contains one of many valuesISNUMBERSUMPRODUCTSEARCH
Range contains specific textCOUNTIF
Range contains specific valueMATCHCOUNTIF
Reverse a list or rangeINDEXROWCOUNTA
Transpose table without zerosIFTRANSPOSE